On Thu, Jan 12, 2012 at 8:47 AM, Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote: > 2012/1/12 <seiliki@xxxxxxxxxxxxx>: >> 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; I don't think the tmp_get* functions are necessary (and even if they were, you should mark them 'immutable'). Also that's unnecessarily verbose. I would write it like this: INSERT INTO test SELECT (r).c1, (r).c2, 'x' FROM ( SELECT UNNEST(ARRAY[ROW(1,'a'),ROW(2,'b')]::my_row_type[]) r ) x; merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
