SQL 如何仅在 Postgres 中备份功能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13758003/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to take backup of functions only in Postgres
提问by vmb
I want to take backup of all functions in my postgres database.How to take backup of functions only in Postgres?
我想备份我的 postgres 数据库中的所有函数。如何只备份 Postgres 中的函数？
回答by Craig Ringer
use pg_getfunctiondef; see system information functions. pg_getfunctiondefwas added in PostgreSQL 8.4.
使用pg_getfunctiondef; 请参阅系统信息功能。pg_getfunctiondef是在 PostgreSQL 8.4 中添加的。
SELECT pg_get_functiondef('proc_name'::regproc);
To dump all functions in a schema you can query the system tables in pg_catalog; say if you wanted everything from public:
要转储模式中的所有功能，您可以查询系统表pg_catalog；说如果你想要一切public：
SELECT pg_get_functiondef(f.oid)
FROM pg_catalog.pg_proc f
INNER JOIN pg_catalog.pg_namespace n ON (f.pronamespace = n.oid)
WHERE n.nspname = 'public';
it's trivial to change the above to say "from all schemas except those beginning with pg_"instead if that's what you want.
如果您想要的话，将上面的内容更改为“从除以pg_”开头的所有模式之外的所有模式都是微不足道的。
In psqlyou can dump this to a file with:
在psql你可以转储这与文件：
psql -At dbname > /path/to/output/file.sql <<"__END__"
... the above SQL ...
__END__
To run the output in another DB, use something like:
要在另一个数据库中运行输出，请使用以下内容：
psql -1 -v ON_ERROR_STOP -f /path/to/output/file.sql target_db_name
If you're replicating functions between DBs like this, though, consider storing the authorative copy of the function definitions as a SQL script in a revision control system like svn or git, preferably packaged as a PostgreSQL extension. See packaging extensions.
但是，如果您像这样在 DB 之间复制函数，请考虑将函数定义的权威副本存储为 svn 或 git 等修订控制系统中的 SQL 脚本，最好打包为 PostgreSQL 扩展。请参阅包装扩展。
回答by dezso
You can't tell pg_dumpto dump only functions. However, you can make a dump without data (-s) and filter it on restoring. Note the -Fcpart: this will produce a file suitable for pg_restore.
你不能告诉pg_dump只转储函数。但是，您可以在没有数据的情况下进行转储 ( -s) 并在恢复时对其进行过滤。请注意该-Fc部分：这将生成一个适用于pg_restore.
First take the dump:
首先取转储：
pg_dump -U username -Fc -s -f dump_test your_database
Then create a list of the functions:
然后创建一个函数列表：
pg_restore -l dump_test | grep FUNCTION > function_list
And finally restore them (-Lspecifies the list file created above):
最后恢复它们（-L指定上面创建的列表文件）：
pg_restore -U username -d your_other_database -L function_list dump_test
