如何从记录中提取值作为 postgresql 中的单独列

Question

如何从记录中提取值作为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

而不是

image 
(3, 4, "jpeg", 7)

我想要有

id | server_id | format | product_id
3  | 4         | jpeg   | 7

没有办法为每个产品仅选择一个图像并直接返回列而不是记录？

Answer 1

尝试一下：

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

或者，您可以这样做：

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

选择所有字段：

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

您也可以这样做，但这使得使用 ROW 的整个练习变得毫无意义，因为您可以删除 ROW 函数并从外部重新选择它。 cte/派生表。我猜测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

Answer 2

只需指定结构的组件：

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

但无论如何，我会重新设计查询并使用联接而不是子条款。

 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

但我相信您必须分别指定不同的所有 p 字段，以确保每个产品仅加载一张图像。

Answer 3

尝试一下，只需最少的修改即可在您现有的代码上工作（如果创建类型对您来说是最小的修改；-）

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

概念验证代码：

首先创建类型：

create type your_type_here as (table_name varchar, column_name varchar)

实际代码：

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)

但我想您应该使用 GROUP BY' 和MAX组合或使用DISTINCT ON` 就像 Daniel 发布的那样

Answer 4

每个表都有一个同名的关联复合类型

https ://www.postgresql.org/docs/current/plpgsql-declarations.html#PLPGSQL-DECLARATION-ROWTYPES

所以，这段代码

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").*

可以工作