From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
Cc: | seiliki(at)so-net(dot)net(dot)tw, pgsql-general(at)postgresql(dot)org |
Subject: | Re: unnest array of row type |
Date: | 2012-01-12 17:14:34 |
Message-ID: | CAFj8pRBSEabuWuay9QVakVMot4VdYok1P4cQ6-rhihAbrp2M5Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2012/1/12 Merlin Moncure <mmoncure(at)gmail(dot)com>:
> On Thu, Jan 12, 2012 at 8:47 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> 2012/1/12 <seiliki(at)so-net(dot)net(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Edwin Quijada | 2012-01-12 17:55:30 | Re: Compiling C function with VC++ for Windows version |
Previous Message | Merlin Moncure | 2012-01-12 17:11:47 | Re: unnest array of row type |