Get definition of function, sequence, type etc. in Postgresql with SQL query

Question

I need the create scripts for PostgreSQL database objects.

I have not access to pg_dump. So I have to get everything with SQL queries. How could I do this?

Answer 1

To get the definition of a function use pg_get_functiondef():

select pg_get_functiondef(oid) from pg_proc where proname = 'foo'; 

There are similar functions to retrieve the definition of an index, a view, a rule and so on. For details see the manual: http://www.postgresql.org/docs/current/static/functions-info.html

Getting the definition of a user type is a bit more tricky. You will need to query information_schema.attributes for that:

select attribute_name, data_type from information_schema.attributes where udt_schema = 'public'   and udt_name = 'footype' order by ordinal_position; 

From that you need to re-assemble the create type statement.

For more details you will need to read through the documentation of the system catalog: http://www.postgresql.org/docs/current/static/catalogs.html

But you should prefer information_schema views if they return the same information.