Get definition of function, sequence, type etc. in Postgresql with SQL query
To get the definition of a function use pg_get_functiondef()
:
select pg_get_functiondef(oid)from pg_procwhere 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_typefrom information_schema.attributeswhere 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.
You will find psql -E
instrumental in your quest for those queries.
It displays the queries psql
uses when executing its backslash-commands - like \df+ myfunc
for details about this function.
Here is a complete sample query using pg_get_functiondef:
WITH funcs AS ( SELECT n.nspname AS schema ,proname AS sproc_name ,proargnames AS arg_names ,t.typname AS return_type ,d.description ,pg_get_functiondef(p.oid) as definition FROM pg_proc p JOIN pg_type t on p.prorettype = t.oid JOIN pg_description d on p.oid = d.objoid JOIN pg_namespace n on n.oid = p.pronamespace WHERE n.nspname = 'some_schema_name_here')SELECT *FROM funcs;;
Note, you should obviously specify the schema name, (or "public" if you are using that schema)