在 Postgresql 上以编程方式生成 DDL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1884758/
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
Generate DDL programmatically on Postgresql
提问by ruipacheco
How can I generate the DDL of a table programmatically on Postgresql? Is there a system query or command to do it? Googling the issue returned no pointers.
如何在 Postgresql 上以编程方式生成表的 DDL？是否有系统查询或命令来执行此操作？谷歌搜索这个问题没有返回任何指针。
采纳答案by ruipacheco
The answer is to check the source code for pg_dump and follow the switches it uses to generate the DDL. Somewhere inside the code there's a number of queries used to retrieve the metadata used to generate the DDL.
答案是检查 pg_dump 的源代码并遵循它用于生成 DDL 的开关。代码中的某处有许多查询用于检索用于生成 DDL 的元数据。
回答by Brain90
Use pg_dumpwith this options:
pg_dump与此选项一起使用：
pg_dump -U user_name -h host database -s -t table_or_view_names -f table_or_view_names.sql
Description:
描述：
-s or --schema-only : Dump only ddl / the object definitions (schema), without data.
-t or --table Dump : Dump only tables (or views or sequences) matching table
Examples:
例子：
-- dump each ddl table that elon build.
$ pg_dump -U elon -h localhost -s -t spacex -t tesla -t solarcity -t boring > companies.sql
Sorry if out of topic. Just wanna help who googling "psql dump ddl" and got this thread.
对不起，如果没有主题。只是想帮助那些在谷歌上搜索“psql dump ddl”并得到这个线程的人。
回答by Greg Hewgill
回答by Wayne Conrad
Why would shelling out to psql not count as "programmatically?" It'll dump the entire schema very nicely.
为什么对 psql 进行炮击不能算作“以编程方式”？它会很好地转储整个模式。
Anyhow, you can get data types (and much more) from the information_schema(8.4 docs referenced here, but this is not a new feature):
无论如何，您可以从information_schema（此处引用 8.4 文档，但这不是新功能）中获取数据类型（以及更多）：
=# select column_name, data_type from information_schema.columns
-# where table_name = 'config';
column_name | data_type
--------------------+-----------
id | integer
default_printer_id | integer
master_host_enable | boolean
(3 rows)
回答by iavci
Here is a good article on how to get the meta information from information schema, http://www.alberton.info/postgresql_meta_info.html.
这是一篇关于如何从信息模式中获取元信息的好文章， http://www.alberton.info/postgresql_meta_info.html。
回答by Vao Tsun
I saved 4 functionsto mock up pg_dump -sbehaviour partially. Based on \d+metacommand. The usage would be smth alike:
我保存了 4 个函数来pg_dump -s部分模拟行为。基于元命令\d+。用法很相似：
\pset format unaligned
select get_ddl_t(schemaname,tablename) as "--" from pg_tables where tableowner <> 'postgres';
Of course you have to create functions prior.
当然，您必须先创建函数。
