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

Поиск
Список
Период
Сортировка
От Valentine Gogichashvili
Тема Re: final patch - plpgsql: for-in-array
Дата
Msg-id AANLkTinnQgj1ubCvaRkqe3FTRuKn9j5=ZHJ8KnKbuxYd@mail.gmail.com
обсуждение исходный текст
Ответ на Re: final patch - plpgsql: for-in-array  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: final patch - plpgsql: for-in-array  (Robert Haas <robertmhaas@gmail.com>)
Список 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;

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@sss.pgh.pa.us> wrote:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> 2010/11/18 Tom Lane <tgl@sss.pgh.pa.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Itagaki Takahiro
Дата:
Сообщение: Re: [JDBC] Support for JDBC setQueryTimeout, et al.
Следующее
От: Magnus Hagander
Дата:
Сообщение: Re: Re: Proposed Windows-specific change: Enable crash dumps (like core files)