在postgres中展平嵌套记录(Flattening nested record in postgres)
如何在外部选择中展平foo列(在PostgreSQL中)?
WITH RECURSIVE t AS ( SELECT row(d.*) as foo FROM some_multicolumn_table as d UNION ALL SELECT foo FROM t WHERE random() < .5 ) SELECT foo FROM t我想要的是输出外部选择中some_multicolumn_table所有列(水平,即作为多行的一行),而不仅仅是单个“记录”列。
怎么做?
How to flatten the foo column in the outer select (in PostgreSQL)?
WITH RECURSIVE t AS ( SELECT row(d.*) as foo FROM some_multicolumn_table as d UNION ALL SELECT foo FROM t WHERE random() < .5 ) SELECT foo FROM tWhat I want is to output all the columns (horizontally, i.e. as a row of multiple columns) of some_multicolumn_table in the outer select, not just a single "record" column.
How to do that?
最满意答案
你不需要那里的ROW构造函数,所以你可以使用(foo).*扩展记录(foo).* :
WITH RECURSIVE t AS ( SELECT d as foo FROM some_multicolumn_table as d UNION ALL SELECT foo FROM t WHERE random() < .5 ) SELECT (foo).* FROM t;虽然这个查询可以简单地写成:
WITH RECURSIVE t AS ( SELECT d.* FROM some_multicolumn_table as d UNION ALL SELECT t.* FROM t WHERE random() < .5 ) SELECT * FROM t;我建议尽量保持简单。 但我认为这只是一个例证。
You don't need the ROW constructor there, and so you can expand the record by using (foo).*:
WITH RECURSIVE t AS ( SELECT d as foo FROM some_multicolumn_table as d UNION ALL SELECT foo FROM t WHERE random() < .5 ) SELECT (foo).* FROM t;Although this query could be simple written as:
WITH RECURSIVE t AS ( SELECT d.* FROM some_multicolumn_table as d UNION ALL SELECT t.* FROM t WHERE random() < .5 ) SELECT * FROM t;And I recommend trying to keep it as simple as possible. But I'm assuming it was just an exemplification.
更多推荐
发布评论