How can I extract values ​​from a record as separate columns in postgresql

Question

How can I extract values ​​from a record as separate comuns in postgresql

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

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

I would like to have

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

Is there a way to select only one image for each product and return the columns directly instead of recording?

Answer 1

Try the following:

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

, 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' and MAX combo or use DISTINCT ON`, ,