Re: final patch - plpgsql: for-in-array

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;

Is it a bug or a feature? And if the second, then any work on optimizing FOR
e IN SELECT UNNEST(a) should probably include FOR e IN SELECT * FROM
UNNEST(a) statement optimizations.

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> > 2010/11/18 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> >> 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 rules
> > related to FOR statement. It can be solved.
>
> No, it can't. The more things that can possibly follow FOR, the less
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>