| From: | seiliki(at)so-net(dot)net(dot)tw |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | unnest array of row type |
| Date: | 2012-01-12 14:25:51 |
| Message-ID: | 20120112142557.A5F20F48F8F@m5.so-net.net.tw |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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.
Thank you in advance!
CN
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Pavel Stehule | 2012-01-12 14:47:55 | Re: unnest array of row type |
| Previous Message | Daniel McGreal | 2012-01-12 13:20:58 | Operator based on data type |