How can I extract the values from a record as individual columns in postgresql

Question

How can I extract the values from a record as individual 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 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?

Corroborant

Why do you need to group those 4 columns to one column? – Rustyrut

I don't need to group them, I just want to have one image for each product but there is no way to put LIMIT on a join as in mysql, the best thing that I achieved was the above query with ROW – Corroborant

Answer 1

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:

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

Rustyrut

that seems to be a lot work for my simple query, so I guess using ROW is not a good solution for my main problem, extract one row from images table for each product row. – Corroborant

indeed, that's why in the first place, i ask you why you need to group columns to one column – Rustyrut

Answer 2

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.

Gerdi

does that really work for you? accessing the fields from within the row using the image.id syntax? I can't get that running for my UDT's... – Kellen

I get 'ERROR: missing FROM-clause entry for table "image"' for the first query, and the second one dosen't work either "ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions" I also think that is faster to execute one subquery for each product (only 10 in this case) instead of joining everything and adding DISTINCT, DISTINCT can be very slow if the tables are large. – Corroborant

wanted to downvote your answer for not trying it out in a proof-of-concept code if the ROW function indeed uses the column names as the property names for struct. please correct your code, or delete it (so you can earn a disciplined badge) your code won't work, try this: select x.table_name from ( select row(table_name, column_name) x from information_schema.columns limit 1 ) as y – Rustyrut

I added parentheses around the image references, in the first SELECT statement. Doing so fixed my problem - I haven't tested the specific expression found here, but in my case the parentheses fixed the "missing FROM-clause" error I (and @Corroborant ) was getting. – Peneus

Answer 3

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

Rustyrut

Answer 4

every table has an associated composite type of the same name

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

Photodynamics

Nice solution for emulating CREATE temp TYPE. Although it would be globally visible anyway. – Targum