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

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 firstselect (x::text::xxx).t, (x::text::xxx).y, (x::text::xxx).zfrom 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 firstb as (select x::text::xxx as w from a)select (w).t, (w).y, (w).zfrom 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 firstb 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).zfrom a

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 imageFROM products pWHERE 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.

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 pWHERE 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