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

sql postgresql ddl
John picture John · Aug 27, 2012 · Viewed 29.6k times · Source

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

a_horse_with_no_name picture a_horse_with_no_name · Aug 27, 2012

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.

Related questions

Give all the permissions to a user on a DB

I would like to give an user all the permissions on a database without making it an admin. The reason why I want to do that is that at the moment DEV and PROD are different DBs on the same …

Read More
PostgreSQL create table if not exists

In a MySQL script you can write: CREATE TABLE IF NOT EXISTS foo ...; ... other stuff ... and then you can run the script many times without re-creating the table. How do you do this in PostgreSQL?

Read More
Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?

I want to create a database which does not exist through JDBC. Unlike MySQL, PostgreSQL does not support create if not exists syntax. What is the best way to accomplish this? The application does not know if the database exists …

Read More