row->ARRAY or row->table casting?
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 :)
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
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?
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
"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
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 likeSELECT (r).a from (select row(tab1.*)::tab1 as r from tab1)x;
yes, it works.
Thanks a lot,
Nico