Fetching object source programmatically

Question

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...

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.

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.

Is there officially maintained or well tested extension, API, pg_dump wrapper or whatever I can use?

@Denis I'm already trying it. But instead of doing it from scratch, maybe there's other open source effort that I can contribute to. To contribute to your logic: "not to difficult" is not the same as "easy". Thanks for your input. — Milos
– Milos, Commented Dec 10, 2013 at 14:26

Denis de Bernardy · Accepted Answer · 2013-12-10 16:53:00Z

1

If you run psql -E, you'll see hidden queries that get run by Postgres to output data definitions.

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 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);

A few views to get you started, if you go the route of posting your stuff on github:

https://gist.github.com/ddebernardy/7893922

(You'll want to create a "system" schema before running the file using \i in psql.)

edited Dec 10, 2013 at 16:53

answered Dec 10, 2013 at 14:37

Denis de Bernardy

79.2k14 gold badges138 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Milos Over a year ago

Good example. select pg_get_functiondef('foo'::regproc); will return function definition, but not "alter function set owner" and setting of privileges. If you execute "pg_dump --schema-only -t foo yourdb", you will get the complete source. When I say complete, I mean when you drop an object and execute it's source, the whole db source remains the same.

Denis de Bernardy Over a year ago

Ya. And if you run \df+ foo after running psql -E, you'll see the owner appear along with the query you need to get it… The system catalog and system information functions are the "officially maintained or well tested" way to do it: it's how psql does it; and how pg_dump does it; and how PGAdmin does it; and how Doctrine does it; and…

Denis de Bernardy Over a year ago

Then again, as much as it's NOT what you need, I'm afraid this probably IS what you'll use. There's no other officially maintained and well tested way to do it. \df aren't so much meta-commands as they're command batches that run queries on the pg_catalog for you. The easiest way to quickly become familiar with what these queries are and to fine-tune them per your own requirements is to show hidden queries by using psql -E. This gives you a starting point to tweak. The harder way is to query the catalog directly: postgresql.org/docs/current/static/catalogs.html

Denis de Bernardy Over a year ago

"Why did PostgreSQL wrap it in meta-commands and pg_dump, but left sys functions so elementary." — If wiki.postgresql.org/wiki/Todo is anything to go by, the main reason is there simply isn't any interest. In FOSS parlance, that usually means core devs are waiting for a volunteer to massage source files such as github.com/postgres/postgres/blob/master/src/bin/psql/… into something that exposes more useful APIs.

Denis de Bernardy Over a year ago

See these two threads on this particular topic. This one on \d: postgresql.org/message-id/[email protected] and this other one on a bug in pg_get_indexdef(): postgresql.org/message-id/[email protected]

|

Collectives™ on Stack Overflow

Fetching object source programmatically

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related