以编程方式获取对象源 [英] Fetching object source programmatically
问题描述
我需要访问对象的完整源代码才能自动执行某些任务。例如：视图的完整来源是视图本身，它是规则，触发器，特权...
I need access to the complete source code of objects in order to automate certain tasks. For example: complete source of view is the view itself, it's rules, triggers, privileges...
通过使用其他PostgreSQL工具（例如PgAdmin，pg_dump，psql），可以容易获取，但是我需要能够通过（sql / plpgsql）函数调用来访问它。
By using different PostgreSQL tools like PgAdmin, pg_dump, psql, this can easily be fetched, but I need to be able to access it through a (sql/plpgsql) function call.
实现如下所示的API并不难：getFunctionSource，getTableSource，getFUnctionSource。但是，对于不同版本的数据库，此代码似乎需要大量维护。
It's not too difficult to implement API looking like this: getFunctionSource, getTableSource, getFUnctionSource. However, it looks like this code would need a lot of maintenance along different versions of database.
是否有经过官方维护或经过良好测试的扩展程序，API，pg_dump包装器或我可以使用的任何东西？
Is there officially maintained or well tested extension, API, pg_dump wrapper or whatever I can use?
推荐答案
如果运行 psql -E ，您将看到隐藏的查询，这些查询由Postgres运行以输出数据
If you run psql -E, you'll see hidden queries that get run by Postgres to output data definitions.
例如，可以通过运行 \df foo 来找到函数的原始源，查询，然后尝试：
A function's raw source, for instance, can be found by running \df foo, reading the query, and subsequently trying:
select prosrc from pg_proc where proname = 'foo'
\sf foo 不会使用该方法产生相关的功能，但有些粗略查阅有关系统信息功能的文档（其中有很多）应该建议它只是一个包装器：
\sf foo doesn't yield the relevant functions using that approach, but a cursory peek at the docs on system information functions (of which there are many) should suggest that it's just a wrapper around:
select pg_get_functiondef('foo'::regproc);
一些入门指南，如果您走在github上发布您的东西的路线：
A few views to get you started, if you go the route of posting your stuff on github:
https://gist.github.com/ddebernardy/7893922
（在使用running运行文件之前，您需要创建系统架构＼i in psql。）
(You'll want to create a "system" schema before running the file using \i in psql.)
这篇关于以编程方式获取对象源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
