Walker Farrow Walker Farrow - 1 year ago 65
SQL Question

How to query a Postgres `RECORD` datatype

I have a query that will return a row as a

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
clause and I need to do so by extracting one of the nodes in the returned
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 Source

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