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 <[email protected]> wrote:
> Pavel Stehule <[email protected]> writes:
> > 2010/11/18 Tom Lane <[email protected]>:
> >> 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 ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
