Walker Farrow Walker Farrow - 3 months ago 16
SQL Question

How to query a Postgres `RECORD` datatype

I have a query that will return a row as a

RECORD
data type from a subquery - see below for example:

select *
from (
select row(st.*) table_rows
from some_table st
) x
where table_rows[0] = 339787


I am trying to further qualify it in the
WHERE
clause and I need to do so by extracting one of the nodes in the returned
RECORD
data type.

When I do the above, I get an error saying:

ERROR: cannot subscript type record because it is not an array


Does anybody know of a way of implementing this?

Answer

Use (row).column_name. And you do not need the row constructor, just refer to the table itself:

select *
from (
    select r
    from some_table r
) x
where (r).column_name = 339787