unnest array of row type

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

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