Postgres Pro
Downloads
Home   >   mailing lists

Thread: row->ARRAY or row->table casting?

row->ARRAY or row->table casting?

From
Nico Sabbi
Date:
Hi,
is there any way to cast a generic row to an array or to a table type?

The example is trivial, but it explains what I'm trying to do:

nb1=# select * from tab1;
 a | t
---+---
 1 | a
 2 | b
 3 | c
(3 rows)


nb1=# select r from (select row(tab1.*) as r from tab1)x;
   r
-------
 (1,a)
 (2,b)
 (3,c)
(3 rows)

nb1=# select r[1] from (select row(tab1.*) as r from tab1)x;
ERROR:  cannot subscript type record because it is not an array

nb1=# select (r::tab1).a from (select row(tab1.*) as r from tab1)x;
ERROR:  cannot cast type record to tab1

The real use case is much more complex than this example of course :)

Re: row->ARRAY or row->table casting?

From
Tom Lane
Date:
Nico Sabbi <nsabbi@officinedigitali.it> writes:
> is there any way to cast a generic row to an array or to a table type?

"row(...)::composite_type" should work in 8.2 and up.

            regards, tom lane

Re: row->ARRAY or row->table casting?

From
Nico Sabbi
Date:
Tom Lane ha scritto:
> Nico Sabbi <nsabbi@officinedigitali.it> writes:
>
>> is there any way to cast a generic row to an array or to a table type?
>>
>
> "row(...)::composite_type" should work in 8.2 and up.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>
I'm using 8.2.4.

What's the right syntax for a case like this?

nb1=# select r.a from (select row(tab1.*)::tab1 as r from tab1)x;
ERROR:  missing FROM-clause entry for table "r"
LINE 1: select r.a from (select row(tab1.*)::tab1 as r from tab1)x;
               ^

I tried many variations (including casting x as tab1) obtaining only
syntax errors.

Thanks,
    Nico

Re: row->ARRAY or row->table casting?

From
Gregory Stark
Date:
"Nico Sabbi" <nsabbi@officinedigitali.it> writes:

> nb1=# select r.a from (select row(tab1.*)::tab1 as r from tab1)x;
> ERROR:  missing FROM-clause entry for table "r"
> LINE 1: select r.a from (select row(tab1.*)::tab1 as r from tab1)x;
>               ^
> I tried many variations (including casting x as tab1) obtaining only syntax
> errors.

r.a would be the column "a" in the table named "r", but the only table in the
FROM list is "x". So you have to use a workaround to make it clear to the
parser that you're referring to the column "r", it would look like

SELECT (r).a from (select row(tab1.*)::tab1 as r from tab1)x;

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com

Re: row->ARRAY or row->table casting?

From
Nico Sabbi
Date:
Gregory Stark ha scritto:
> "Nico Sabbi" <nsabbi@officinedigitali.it> writes:
>
>
>> nb1=# select r.a from (select row(tab1.*)::tab1 as r from tab1)x;
>> ERROR:  missing FROM-clause entry for table "r"
>> LINE 1: select r.a from (select row(tab1.*)::tab1 as r from tab1)x;
>>               ^
>> I tried many variations (including casting x as tab1) obtaining only syntax
>> errors.
>>
>
> r.a would be the column "a" in the table named "r", but the only table in the
> FROM list is "x". So you have to use a workaround to make it clear to the
> parser that you're referring to the column "r", it would look like
>
> SELECT (r).a from (select row(tab1.*)::tab1 as r from tab1)x;
>
>
yes, it works.

Thanks a lot,
    Nico