Export only views in Postgres

Question

Is there any way to export only the views from a Postgres schema?

I'm using Postgres 8.4.

Thank you.

Answer 1

If you have every view prefixed by certain prefix, you can use this command:

pg_dump -s -t 'prefix*' dbname > db.dump

or you can use -t switch as many as possible with names of views.

See manpage for pg_dump, on the end are examples.

Answer 2

There's no direct flag to do this, but using our favourite query-the-schema-to-generate-a-command technique:

select string_agg( '-t ' || quote_ident(nspname) || '.' || quote_ident(relname), ' ' )
  from pg_class join pg_namespace on pg_namespace.oid = pg_class.relnamespace
  where relkind = 'v' and not (nspname ~ '^pg_' or nspname = 'information_schema');

This will generate a string that can be used with a pg_dump command, e.g.:

 -t media.duplicated_component -t adv.advert_view_distribution 

Which you could then splice into a command line directly:

pg_dump $(psql -c "select string_agg(...etc...)" db) db