在postgres中展平嵌套记录(Flattening nested record in postgres)

在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 t

What 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.