Обсуждение: unnest array of row type

Поиск
Список
Период
Сортировка

unnest array of row type

От
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.
BecauseI wonder "INSERT INTO ... SELECT ... FROM" being faster than "LOOP ... END LOOP;", I raise this question. 

Thank you in advance!
CN

Re: unnest array of row type

От
Pavel Stehule
Дата:
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

Re: unnest array of row type

От
Merlin Moncure
Дата:
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

Re: unnest array of row type

От
Pavel Stehule
Дата:
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

Re: unnest array of row type

От
seiliki@so-net.net.tw
Дата:
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