Re: final patch - plpgsql: for-in-array - Mailing list pgsql-hackers
| From | Valentine Gogichashvili |
|---|---|
| Subject | Re: final patch - plpgsql: for-in-array |
| Date | |
| Msg-id | AANLkTinnQgj1ubCvaRkqe3FTRuKn9j5=ZHJ8KnKbuxYd@mail.gmail.com Whole thread Raw |
| In response to | Re: final patch - plpgsql: for-in-array (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: final patch - plpgsql: for-in-array
|
| List | pgsql-hackers |
Hi,
with the FOR e IN SELECT UNNEST(a) construct there is an issue again related to the unresting of composite type arrays:
BEGIN;
CREATE TYPE truple AS (i integer, a text, b text);
DO $SQL$
DECLARE
start_time timestamp;
t truple;
ta truple[] := ARRAY( select ROW(s.i, 'A' || (s.i)::text, 'B' || (s.i)::text )::truple from generate_series(1, 10000) as s(i) );
i integer := 1;
BEGIN
start_time := clock_timestamp();
FOR t IN SELECT UNNEST(ta) LOOP
raise info 't is %', t;
i := i + 1;
END LOOP;
RAISE INFO 'looped in %', clock_timestamp() - start_time;
END;
$SQL$;
ROLLBACK;
fails with ERROR: invalid input syntax for integer: "(1,A1,B1)"
CONTEXT: PL/pgSQL function "inline_code_block" line 8 at FOR over SELECT rows
So to UNNEST such an array one has to SELECT * FROM UNNEST(a) to be able loop there like:
BEGIN;
CREATE TYPE truple AS (i integer, a text, b text);
DO $SQL$
DECLARE
start_time timestamp;
t truple;
ta truple[] := ARRAY( select ROW(s.i, 'A' || (s.i)::text, 'B' || (s.i)::text )::truple from generate_series(1, 10000) as s(i) );
i integer := 1;
BEGIN
start_time := clock_timestamp();
FOR t IN SELECT * FROM UNNEST(ta) LOOP
raise info 't is %', t;
i := i + 1;
END LOOP;
RAISE INFO 'looped in %', clock_timestamp() - start_time;
END;
$SQL$;
ROLLBACK;
Also, would the suggested FOR-IN-ARRAY construct loop in such a composite type arrays?
Best regards,
-- Valenine Gogichashvili
On Thu, Nov 18, 2010 at 8:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> The problem here is that FOR is a syntactic choke point: it's already
>> overloaded with several different sub-syntaxes that are quite difficult
>> to separate. Adding another one makes that worse, with the consequences
>> that we might misinterpret the user's intent, leading either to
>> misleading/unhelpful error messages or unexpected runtime behavior.> yes, this argument is correct - but we can rearange a parser rulesNo, it can't. The more things that can possibly follow FOR, the less
> related to FOR statement. It can be solved.
likely that you correctly guess which one the user had in mind when
faced with something that's not quite syntactically correct. Or maybe
it *is* syntactically correct, only not according to the variant that
the user thought he was invoking. We've seen bug reports of this sort
connected with FOR already; in fact I'm pretty sure you've responded to
a few yourself. Adding more variants *will* make it worse. We need
a decent return on investment for anything we add here, and this
proposal just doesn't offer enough benefit.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
pgsql-hackers by date: