PostgreSql , extract schema objects DDL to separate SQL file

Question

I want to export all objects DDL to separate file example (table_a_create.sql, view_b_create.sql, trigger_c_create.sql, table_contraints.sql ...)

I was trying with pg_dump but it only exports to one file for the whole schema.

I read some questions about this on stackoverflow but still not enough for my requirement

Ex: How to dump PostgreSQL database structure (each object in separate file)

Is there any way to do it? I'm using Windows

What do you consider to be an "object"? Is a column part of a table, or a separate object? What about contraints? Or triggers? — Bergi
– Bergi, Commented Nov 15, 2019 at 2:02
@Bergi I mean table, view, constraint, trigger definition only. No data, no function need — Ryo
– Ryo, Commented Nov 15, 2019 at 2:05
You can try with the -t option but this is not safe as the objects that this table depends on will not be dumped so there is no guarantee that the table definition can be successfully restored. — IVO GELOV
– IVO GELOV, Commented Nov 15, 2019 at 9:20
@Ryo Did you ever find a way to do this? I've looked everywhere and have not found a tool that can script out every table, procedure, and function each in their own separate file and into a hierarchical folder structure. — JCDrumKing
– JCDrumKing, Commented Jul 10, 2024 at 17:23

R. Du · Accepted Answer · 2020-04-02 10:05:32Z

If you are on the client machine, you can put this in a SQL script (e.g. export_plpgsql.sql) :

\pset tuples_only on
\pset footer off
\set QUIET on
\pset format unaligned
\set QUIET off

SELECT '\echo ''* Export '||(CASE proKind WHEN 'f' THEN 'Function' ELSE 'Procedure' END)||' : '||proName||''''
       ||chr(10)||'\copy (SELECT pg_get_functiondef('||p.oid||')) TO '''||:'export_path'||'/'||upper(proName)
       ||(CASE proKind WHEN 'f' THEN '.fct' ELSE '.prc' END)||''' WITH CSV;' as export_routine
FROM pg_proc p
WHERE proNamespace = (SELECT oid FROM pg_namespace WHERE nspName = lower(:'schema_name'))
ORDER BY proName;

and call it using 2 arguments : schema_name and export_path, for example :

psql -U my_ -d my_db -v schema_name=my_schema -v export_path=C:/temp/export_PG -f export_plpgsql.sql > C:\temp\export_plpgsql.gen.sql

This will generate a script containing all the exports command for your plpgsql routines, e.g.

\copy (SELECT pg_get_functiondef(51296)) TO 'C:/temp/export_PG/my_procedure.prc' WITH CSV;

Last step : run the generated script

psql -U my_ -d my_db -f C:\temp\export_plpgsql.gen.sql

It will generate a .prc file for each procedure and a .fct file for each function. NB: You may have to refine the script as you can have other kind of functions (proKind) in pg_proc view.

Collectives™ on Stack Overflow

PostgreSql , extract schema objects DDL to separate SQL file

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related