如何从记录中提取值作为 postgresql 中的单个列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4700661/
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 can I extract the values from a record as individual columns in postgresql
提问by codeassembly
How can I extract the values from a record as individual comuns in postgresql
如何从记录中提取值作为 postgresql 中的单个 comuns
SELECT
p.*,
(SELECT ROW(id,server_id,format,product_id) FROM products_images pi WHERE pi.product_id = p.id LIMIT 1) AS image
FROM products p
WHERE p.company = 1 ORDER BY id ASC LIMIT 10
Instead of
代替
image
(3, 4, "jpeg", 7)
I would like to have
我想拥有
id | server_id | format | product_id
3 | 4 | jpeg | 7
Is there any way of selecting only one image for each product and return the columns directly instead of a record?
有没有办法为每个产品只选择一个图像并直接返回列而不是记录？
回答by Michael Buen
Try this:
尝试这个：
create type xxx as (t varchar, y varchar, z int);
with a as
(
select row(table_name, column_name, (random() * 100)::int) x
from information_schema.columns
)
-- cannot cast directly to xxx, should cast to text first
select (x::text::xxx).t, (x::text::xxx).y, (x::text::xxx).z
from a
Alternatively, you can do this:
或者，您可以这样做：
with a as
(
select row(table_name, column_name, (random() * 100)::int) x
from information_schema.columns
),
-- cannot cast directly to xxx, should cast to text first
b as (select x::text::xxx as w from a)
select
(w).t, (w).y, (w).z
from b
To select all fields:
要选择所有字段：
with a as
(
select row(table_name, column_name, (random() * 100)::int) x
from information_schema.columns
),
-- cannot cast directly to xxx, should cast to text first
b as (select x::text::xxx as w from a)
select
(w).*
from b
You can do this too, but this makes the whole exercise of using ROW a pointless one when you can just remove the ROW function and re-pick it up from outside of cte/derived table. I surmised the OP's ROW came from a function; for which he should use the codes above, not the following:
您也可以这样做，但是当您可以删除 ROW 函数并从 cte/派生表外部重新获取它时，这使得使用 ROW 的整个练习变得毫无意义。我推测 OP 的 ROW 来自一个函数；他应该使用上面的代码，而不是以下代码：
with a as
(
select row(table_name, column_name, (random() * 100)::int)::xxx x
from information_schema.columns
)
select
(x).t, (x).y, (x).z
from a
回答by Daniel
Just specify the components of your struct:
只需指定结构的组件：
SELECT a,b,c,(image).id, (image).server_id, ...
FROM (
SELECT
p.*,
(SELECT ROW(id,server_id,format,product_id) FROM products_images pi WHERE pi.product_id = p.id LIMIT 1) AS image
FROM products p
WHERE p.company = 1 ORDER BY id ASC LIMIT 10
) as subquery
But anyway, I would rework the query and use a join instead of a subclause.
但无论如何，我会重新编写查询并使用连接而不是子条款。
SELECT DISTINCT ON (p.*) p.*,
p.id,pi.server_id,pi.format,pi.product_id
FROM products p
LEFT JOIN product_images pi ON pi.product_id = p.id
WHERE p.company = 1
ORDER BY id ASC
LIMIT 10
But I believe you have to specify all the p-fields in the distinct separately to ensure just one image is loaded per product.
但我相信您必须单独指定不同的所有 p 字段，以确保每个产品只加载一个图像。
回答by Michael Buen
Try this, will work on your existing code with minimal modification(if creating a type is a minimal modification for you ;-)
试试这个，只需最少的修改就可以处理你现有的代码（如果创建一个类型对你来说是一个最小的修改;-)
create type image_type as (id int, server_id int, format varchar, product_id int);
SELECT
p.*,
( (SELECT ROW(id,server_id,format,product_id)
FROM products_images pi
WHERE pi.product_id = p.id LIMIT 1)::text::image_type ).*
FROM products p
WHERE p.company = 1 ORDER BY id ASC LIMIT 10
Proof-of-concept code:
概念验证代码：
Create type first:
首先创建类型：
create type your_type_here as (table_name varchar, column_name varchar)
Actual code:
实际代码：
select
a.b,
( (select row(table_name, column_name)
from information_schema.columns limit 1)::text::your_type_here ).*
from generate_series(1,10) as a(b)
But I guess you should tackle it with GROUP BY' andMAXcombo or useDISTINCT ON` like what Daniel have posted
但我想你应该用GROUP BY' andMAX combo or useDISTINCT ON`来解决它，就像 Daniel 发布的那样
回答by iliyesku
every table has an associated composite type of the same name
每个表都有一个关联的同名复合类型
https://www.postgresql.org/docs/current/plpgsql-declarations.html#PLPGSQL-DECLARATION-ROWTYPES
https://www.postgresql.org/docs/current/plpgsql-declarations.html#PLPGSQL-DECLARATION-ROWTYPES
So, this code
所以，这段代码
drop table if exists "#typedef_image"
;
create temp table "#typedef_image"(
id int,
server_id int,
format text,
product_id int
)
;
select (row(3, 4, 'jpeg', 7)::"#typedef_image").*
will work
将工作
