Thread: unnest array of row type
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. BecauseI wonder "INSERT INTO ... SELECT ... FROM" being faster than "LOOP ... END LOOP;", I raise this question. Thank you in advance! CN
2012/1/12 <seiliki@so-net.net.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
On Thu, Jan 12, 2012 at 8:47 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > 2012/1/12 <seiliki@so-net.net.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; 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
2012/1/12 Merlin Moncure <mmoncure@gmail.com>: > On Thu, Jan 12, 2012 at 8:47 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >> 2012/1/12 <seiliki@so-net.net.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; > > 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 sure - this is better Pavel
What I really intend to do is slightly more complicate than the original code. I need to iterate RECORD variable in PL/pgSQL.By combining both ideas from Pavel and Merlin, I get the following working function. CREATE FUNCTION test() RETURNS VOID AS $$ DECLARE rec RECORD; BEGIN CREATE TABLE test (c1 SMALLINT,c2 TEXT,c3 TEXT); CREATE TYPE my_row_type AS (c1 SMALLINT,c2 TEXT); FOR rec IN SELECT ARRAY[ROW(1::SMALLINT,'a'::TEXT),ROW(2::SMALLINT,'b'::TEXT)] AS array_of_rows,'x' AS x LOOP INSERT INTO test SELECT (r).c1,(r).c2,rec.x FROM ( SELECT (c1,c2)::my_row_type AS r FROM UNNEST(rec.array_of_rows) AS (c1 SMALLINT,c2 TEXT) ) s; END LOOP; DROP TYPE my_row_type; DROP TABLE test; END $$ LANGUAGE PLPGSQL VOLATILE; Pavel and Merlin, thank you! CN > -----Original Message----- > From: Merlin Moncure > Sent: Fri, Jan 13 2012 01:13:09 CST > To: Pavel Stehule > Subject: Re: [GENERAL] unnest array of row type > > On Thu, Jan 12, 2012 at 8:47 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > > 2012/1/12 <seiliki@so-net.net.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; > > 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general