From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | seiliki(at)so-net(dot)net(dot)tw |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: unnest array of row type |
Date: | 2012-01-12 14:47:55 |
Message-ID: | CAFj8pRBmQbEAG6h_SRWkK=fjf+mbfdoSb4i7n99FH4UN0g=7gA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2012/1/12 <seiliki(at)so-net(dot)net(dot)tw>:
> Hi!
>
> CREATE TYPE my_row_type AS (c1 SMALLINT,c2 TEXT);
>
> CREATE FUNCTION tmp_get_c1(my_row_type) RETURNS SMALLINT AS 'SELECT $1.c1' LANGUAGE SQL;
>
> CREATE FUNCTION tmp_get_c2(my_row_type) RETURNS TEXT AS 'SELECT $1.c2' LANGUAGE SQL;
>
> CREATE TABLE test (c1 SMALLINT,c2 TEXT,c3 TEXT);
>
> INSERT INTO test
> SELECT tmp_get_c1(r),tmp_get_c2(r),'x'
> FROM (
> SELECT UNNEST(ARRAY[ROW(1::SMALLINT,'a'::TEXT),ROW(2::SMALLINT,'b'::TEXT)]) AS r
> ) s;
>
> I get error "record type has not been registered" from the previous INSERT.
>
> I have tested version 9.1 and have confirmed that PL/PGSQL "FOREACH ... IN ARRAY ... LOOP ... END LOOP;" does the job. Because I wonder "INSERT INTO ... SELECT ... FROM" being faster than "LOOP ... END LOOP;", I raise this question.
>
insert into test select tmp_get_c1(r), tmp_get_c2(r), 'x' from (SELECT
(x,y)::my_row_type as r from
UNNEST(ARRAY[ROW(1::SMALLINT,'a'::TEXT),ROW(2::SMALLINT,'b'::TEXT)])
AS (x smallint, y text)) x;
regards
Pavel Stehule
> Thank you in advance!
> CN
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel McGreal | 2012-01-12 15:36:14 | Operator based on data type |
Previous Message | seiliki | 2012-01-12 14:25:51 | unnest array of row type |