Hello

I looked on some constructs that helps with iteration over array in
plpgsql. I propose a following syntax:

FOR var IN [array variable | array expression]
LOOP
..
END LOOP

var - declared variable - theoretically we can a detect var type from
array type, but it needs a early expression an analyze (not used in
PL/pgSQL), so var should be declared before. This construct ensure
iteration over all items of array. When somebody needs a subscripts
from some dimension, then he can use a proposed function "subscripts".

so iteration over two dimensional array can be written:

DECLARE
i integer;
j integer;
BEGIN
FOR i IN subscripts(myarray, 1) LOOP
FOR j IN subscripts(myarray, 2) LOOP
RAISE NOTICE 'myarray[%,%] = %', i, j, myarray[i,j];
END LOOP;
END LOOP;

When input array is multidimensional, then this array is flattened -
order of iteration is specified by physical store of items inside the
array. This construct iterate over all items of input array - it has a
same behave as "unnest" function.

some examples:

DECLARE
v integer;
a int[] := ARRAY[2,3.4,5];
BEGIN
FOR val IN a
LOOP
RAISE NOTICE '%', val; -- produce 2,3,4,5
END LOOP;

FOR val IN subscripts(a, 1)
LOOP
RAISE NOTICE '%', val; -- produce 1,2,3,4
END LOOP;

FOR val IN subscripts(a,1)
LOOP
RAISE NOTICE '%', a[val]; -- produce 2,3,4,5
END LOOP;
END;

Comments, ideas?

Regards

Pavel Stehule

Search Discussions

  • Itagaki Takahiro at Sep 28, 2010 at 9:25 am

    On Tue, Sep 28, 2010 at 3:24 PM, Pavel Stehule wrote:
    I looked on some constructs that helps with iteration over array in
    plpgsql. I propose a following syntax:

    FOR var IN [array variable | array expression]
    What is the benefits compared with
    FOR ... IN SELECT unnest(array) or generate_subscripts(array) ?

    --
    Itagaki Takahiro
  • Pavel Stehule at Sep 28, 2010 at 10:19 am

    2010/9/28 Itagaki Takahiro <itagaki.takahiro@gmail.com>:
    On Tue, Sep 28, 2010 at 3:24 PM, Pavel Stehule wrote:
    I looked on some constructs that helps with iteration over array in
    plpgsql. I propose a following syntax:

    FOR var IN [array variable | array expression]
    What is the benefits compared with
    FOR ... IN SELECT unnest(array) or generate_subscripts(array) ?
    the speed

    SELECT unnest() is full query, but array_expression is just simple
    query and can be evaluated by
    exec_eval_simple_expr - it can be significantly times faster.
    CREATE OR REPLACE FUNCTION f1()
    RETURNS void AS $$
    DECLARE a int[] := ARRAY[1,2,3,4];
    s int;
    BEGIN
    FOR i IN 1..10000 LOOP
    s := 0;
    FOR j IN array_lower(a,1)..array_upper(a,1)
    LOOP
    s := s + a[j];
    END LOOP;
    END LOOP;
    END;
    $$ LANGUAGE plpgsql;

    take about 255ms

    CREATE OR REPLACE FUNCTION f1()
    RETURNS void AS $$
    DECLARE a int[] := ARRAY[1,2,3,4]; j int;
    s int;
    BEGIN
    FOR i IN 1..10000 LOOP
    s := 0;
    FOR j IN SELECT unnest(a)
    LOOP
    s := s + j;
    END LOOP;
    END LOOP;
    END;
    $$ LANGUAGE plpgsql;

    it takes abou 1000ms

    Regards

    Pavel Stehule

    --
    Itagaki Takahiro
  • Peter Geoghegan at Sep 28, 2010 at 12:45 pm

    2010/9/28 Itagaki Takahiro <itagaki.takahiro@gmail.com>:
    On Tue, Sep 28, 2010 at 3:24 PM, Pavel Stehule wrote:
    I looked on some constructs that helps with iteration over array in
    plpgsql. I propose a following syntax:

    FOR var IN [array variable | array expression]
    What is the benefits compared with
    FOR ... IN SELECT unnest(array) or generate_subscripts(array) ?
    the speed
    Not to mention that it's far more aesthetically pleasing, and makes
    the statement immediately understandable to people unfamiliar with the
    plpgsql idioms you describe.

    --
    Regards,
    Peter Geoghegan
  • Tom Lane at Sep 28, 2010 at 2:34 pm

    Pavel Stehule writes:
    I looked on some constructs that helps with iteration over array in
    plpgsql. I propose a following syntax:
    FOR var IN [array variable | array expression]
    LOOP
    I don't have any opinion about whether the functionality proposed here
    is worth the trouble, but I do have an opinion about that syntax: it's
    an awful choice. plpgsql has enough trouble already distinguishing
    between integer for-loops and query for-loops, not to mention trouble
    in producing a helpful error message when somebody gets either of those
    constructs slightly wrong. Providing a variant where a single
    expression can follow IN will make both of those problems an order of
    magnitude worse. As an example, is this a for-in-query or a
    for-in-array?

    FOR v IN (SELECT arraycol FROM tab) LOOP ...

    Either answer is plausible depending on whether you assume the
    parentheses make it a subquery.

    Pick something less easily confusable with the existing constructs.

    regards, tom lane
  • Robert Haas at Sep 28, 2010 at 2:41 pm

    On Tue, Sep 28, 2010 at 10:34 AM, Tom Lane wrote:
    Pavel Stehule <pavel.stehule@gmail.com> writes:
    I looked on some constructs that helps with iteration over array in
    plpgsql. I propose a following syntax:
    FOR var IN [array variable | array expression]
    LOOP
    I don't have any opinion about whether the functionality proposed here
    is worth the trouble, but I do have an opinion about that syntax: it's
    an awful choice.
    I agree, on both points.

    It's nice to try to reduce the excess verbosity that is IMHO the
    biggest usability issue with PL/pgsql, but I can't help wondering
    whether we're trying to dam the Nile with chicken wire.

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise Postgres Company
  • David E. Wheeler at Sep 28, 2010 at 4:17 pm

    On Sep 28, 2010, at 7:41 AM, Robert Haas wrote:

    I don't have any opinion about whether the functionality proposed here
    is worth the trouble, but I do have an opinion about that syntax: it's
    an awful choice.
    I agree, on both points.

    It's nice to try to reduce the excess verbosity that is IMHO the
    biggest usability issue with PL/pgsql, but I can't help wondering
    whether we're trying to dam the Nile with chicken wire.
    I would really like to have this functionality. Iterating over arrays is something I do in PL/pgSQL a *lot*.

    I see two ways to handle syntax:

    1. A new keyword. Some options:
    + FOREACH (too close to FOR?)
    + ITERATE (ew)

    2. Require the subscripts() function as syntax.

    Thoughts?

    Best,

    David
  • Kevin Grittner at Sep 28, 2010 at 4:28 pm

    Robert Haas wrote:
    FOR var IN [array variable | array expression]
    LOOP
    I don't have any opinion about whether the functionality proposed
    here is worth the trouble, but I do have an opinion about that
    syntax: it's an awful choice.
    I agree, on both points.
    How about distinguishing it this way:?

    FOR var IN ARRAY array_expression LOOP

    -Kevin
  • Tom Lane at Sep 28, 2010 at 4:35 pm

    "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
    FOR var IN [array variable | array expression]
    LOOP
    How about distinguishing it this way:?
    FOR var IN ARRAY array_expression LOOP
    That occurred to me too, but it's got a small problem: it's not
    impossible for ARRAY to be the first token of a valid scalar expression.

    But I guess you could get around that if you had to by putting the ARRAY
    expression inside parens, and it would be a pretty darn unusual case
    anyway. So this is probably the best choice. I'm not thrilled with
    David's suggestions of using FOREACH or ITERATE --- using a different
    initial keyword makes it awkward to make generic statements about "all
    types of FOR loop".

    regards, tom lane
  • Pavel Stehule at Sep 28, 2010 at 7:49 pm

    2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:
    "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
    FOR var IN [array variable | array expression]
    LOOP
    How about distinguishing it this way:?
    FOR var IN ARRAY array_expression LOOP
    I see one problem - when you can use a constant array, then you will
    write two keywords ARRAY

    FOR var IN ARRAY ARRAY[...] LOOP

    iteration over cursor is supported now, and you don't write

    FOR var IN CURSOR cursorvar
    That occurred to me too, but it's got a small problem: it's not
    impossible for ARRAY to be the first token of a valid scalar expression. yes
    But I guess you could get around that if you had to by putting the ARRAY
    expression inside parens, and it would be a pretty darn unusual case
    anyway.  So this is probably the best choice.
    I don't agree - There isn't reason for complicating proposed syntax.
    The situation is relative simple and we are able to print a correct
    messages.

    regards

    Pavel Stehule

    I'm not thrilled with
    David's suggestions of using FOREACH or ITERATE --- using a different
    initial keyword makes it awkward to make generic statements about "all
    types of FOR loop".

    regards, tom lane
  • Tom Lane at Sep 28, 2010 at 8:19 pm

    Pavel Stehule writes:
    2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:
    But I guess you could get around that if you had to by putting the ARRAY
    expression inside parens, and it would be a pretty darn unusual case
    anyway.  So this is probably the best choice.
    I don't agree - There isn't reason for complicating proposed syntax.
    Yes, there is. The syntax you propose is flat out ambiguous: there are
    two possible legal interpretations of some commands. That's not
    acceptable, especially not when it's so easily fixed.

    regards, tom lane
  • Pavel Stehule at Sep 28, 2010 at 8:25 pm

    2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:
    Pavel Stehule <pavel.stehule@gmail.com> writes:
    2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:
    But I guess you could get around that if you had to by putting the ARRAY
    expression inside parens, and it would be a pretty darn unusual case
    anyway.  So this is probably the best choice.
    I don't agree - There isn't reason for complicating proposed syntax.
    Yes, there is.  The syntax you propose is flat out ambiguous: there are
    two possible legal interpretations of some commands.  That's not
    acceptable, especially not when it's so easily fixed.
    what are you thinking? The subquery cannot be interpreted different.
    There are not possible use a isolated subquery as query. And subquery
    have to return one row, one column.

    Pavel

    regards, tom lane
  • Tom Lane at Sep 28, 2010 at 8:38 pm

    Pavel Stehule writes:
    2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:
    Yes, there is.  The syntax you propose is flat out ambiguous: there are
    two possible legal interpretations of some commands.
    what are you thinking? The subquery cannot be interpreted different.
    Sure it can: it could be a parenthesized top-level query. In fact,
    that's what plpgsql will assume if you feed it that syntax today.

    regards, tom lane
  • Pavel Stehule at Sep 28, 2010 at 8:42 pm

    2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:
    Pavel Stehule <pavel.stehule@gmail.com> writes:
    2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:
    Yes, there is.  The syntax you propose is flat out ambiguous: there are
    two possible legal interpretations of some commands.
    what are you thinking? The subquery cannot be interpreted different.
    Sure it can: it could be a parenthesized top-level query.  In fact,
    that's what plpgsql will assume if you feed it that syntax today.
    no - there are not any legal construct FOR r IN (..)

    I believe so we can find more than one similar undocumented features,
    like this - so it means so plpgsql will be a buggy?
    regards, tom lane
  • Tom Lane at Sep 28, 2010 at 9:03 pm

    Pavel Stehule writes:
    2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:
    Sure it can: it could be a parenthesized top-level query.  In fact,
    that's what plpgsql will assume if you feed it that syntax today.
    no - there are not any legal construct FOR r IN (..)
    You are simply wrong, sir, and I suggest that you go read the SQL
    standard until you realize that. Consider for example

    for r in (SELECT ... FROM a UNION SELECT ... FROM b) INTERSECT (SELECT ... FROM c) LOOP ...

    The parentheses here are not merely legal, they are *necessary*, else
    the semantics of the UNION/INTERSECT operations change.

    regards, tom lane
  • Pavel Stehule at Sep 29, 2010 at 4:36 am

    2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:
    Pavel Stehule <pavel.stehule@gmail.com> writes:
    2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:
    Sure it can: it could be a parenthesized top-level query.  In fact,
    that's what plpgsql will assume if you feed it that syntax today.
    no - there are not any legal construct FOR r IN (..)
    You are simply wrong, sir, and I suggest that you go read the SQL
    standard until you realize that.  Consider for example

    for r in (SELECT ... FROM a UNION SELECT ... FROM b) INTERSECT (SELECT ... FROM c) LOOP ...

    The parentheses here are not merely legal, they are *necessary*, else
    the semantics of the UNION/INTERSECT operations change.
    ok, then probably one variant is for-in-array array_expr. Is there agreement?

    Regards

    Pavel Stehule



    regards, tom lane
  • Alvaro Herrera at Sep 29, 2010 at 1:16 am

    Excerpts from Kevin Grittner's message of mar sep 28 12:28:12 -0400 2010:

    How about distinguishing it this way:?

    FOR var IN ARRAY array_expression LOOP
    What about

    FOR EACH var IN array_expr LOOP ...

    I think this requires reserving EACH, which could cause a regression for
    working code. Maybe there's a way to make it work?

    --
    Álvaro Herrera <alvherre@commandprompt.com>
    The PostgreSQL Company - Command Prompt, Inc.
    PostgreSQL Replication, Consulting, Custom Development, 24x7 support
  • Darren Duncan at Sep 29, 2010 at 1:31 am

    Alvaro Herrera wrote:
    What about

    FOR EACH var IN array_expr LOOP ...

    I think this requires reserving EACH, which could cause a regression for
    working code. Maybe there's a way to make it work?
    Code that quotes all of its identifiers, such as with:

    FOR EACH "var" IN "array_expr" LOOP ...

    ... would also gain a significant amount of future-proofing since then the
    language can add keywords at will, without there being conflicts with
    user-defined identifiers.

    Similarly, quoting identifiers also carries present-day advantages as then one
    can name identifiers whatever is most suitably descriptive for them without
    worrying whether the language has a pre-defined meaning for the used words.

    The quoting also has the nice bonus of making them case-sensitive.

    -- Darren Duncan
  • Andrew Dunstan at Sep 29, 2010 at 2:27 am

    On 09/28/2010 09:31 PM, Darren Duncan wrote:
    Alvaro Herrera wrote:
    What about

    FOR EACH var IN array_expr LOOP ...

    I think this requires reserving EACH, which could cause a regression for
    working code. Maybe there's a way to make it work?
    Code that quotes all of its identifiers, such as with:

    FOR EACH "var" IN "array_expr" LOOP ...

    ... would also gain a significant amount of future-proofing since then
    the language can add keywords at will, without there being conflicts
    with user-defined identifiers.

    Similarly, quoting identifiers also carries present-day advantages as
    then one can name identifiers whatever is most suitably descriptive
    for them without worrying whether the language has a pre-defined
    meaning for the used words.

    The quoting also has the nice bonus of making them case-sensitive.
    This doesn't help in the least if the array is an expression rather than
    simply a variable - we're not going to start quoting expressions.

    cheers

    andrew
  • Darren Duncan at Sep 29, 2010 at 5:20 am

    Andrew Dunstan wrote:
    On 09/28/2010 09:31 PM, Darren Duncan wrote:

    Code that quotes all of its identifiers, such as with:

    FOR EACH "var" IN "array_expr" LOOP ...
    This doesn't help in the least if the array is an expression rather than
    simply a variable - we're not going to start quoting expressions.
    I wrote that wrong. I should have said "array_var" not "array_expr". I am
    certainly not advocating quoting expressions, and didn't mean to imply that
    here. My point was that if a token is always interpreted as a keyword rather
    than an identifier when there is ambiguity, then quoting would let users name an
    identifier "each" or "EACH". In any event, I will not push this since it
    doesn't address the real issue of language changes not breaking the general case
    of legacy code; it only says how users can insulate themselves. -- Darren Duncan
  • Darren Duncan at Sep 29, 2010 at 1:43 am

    Alvaro Herrera wrote:
    What about

    FOR EACH var IN array_expr LOOP ...

    I think this requires reserving EACH, which could cause a regression for
    working code. Maybe there's a way to make it work?
    What about saying FOR-EACH instead?

    A good general solution that I'd expect to not cause regressions is to separate
    multiple-word keywords with dashes rather than spaces.

    Since unquoted identifiers don't have dashes, I think, and moreover because the
    whole FOR-EACH would occupy the first position of the statement rather than the
    first two, there should be no ambiguity.

    Parsing should be easier, too, because keywords formatted like this would just
    be a single term rather than having infinite variations due to embedded whitespace.

    -- Darren Duncan
  • Andrew Dunstan at Sep 29, 2010 at 2:32 am

    On 09/28/2010 09:43 PM, Darren Duncan wrote:
    Alvaro Herrera wrote:
    What about

    FOR EACH var IN array_expr LOOP ...

    I think this requires reserving EACH, which could cause a regression for
    working code. Maybe there's a way to make it work?
    What about saying FOR-EACH instead?

    A good general solution that I'd expect to not cause regressions is to
    separate multiple-word keywords with dashes rather than spaces.

    Since unquoted identifiers don't have dashes, I think, and moreover
    because the whole FOR-EACH would occupy the first position of the
    statement rather than the first two, there should be no ambiguity.

    Parsing should be easier, too, because keywords formatted like this
    would just be a single term rather than having infinite variations due
    to embedded whitespace.
    This would actually make the parsing infinitely more ugly, not less. And
    we are not gong to start introducing non-alphabetic characters into
    keywords. It is also, as Tom noted about the earlier version, without
    any obvious connection to array processing.

    cheers

    andrew
  • Tom Lane at Sep 29, 2010 at 1:55 am

    Alvaro Herrera writes:
    Excerpts from Kevin Grittner's message of mar sep 28 12:28:12 -0400 2010:
    How about distinguishing it this way:?
    FOR var IN ARRAY array_expression LOOP
    What about
    FOR EACH var IN array_expr LOOP ...
    That doesn't seem to have any obvious connection to looping over array
    elements, as opposed to some other kind of iteration.

    regards, tom lane
  • Pavel Stehule at Sep 28, 2010 at 7:42 pm

    2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:
    Pavel Stehule <pavel.stehule@gmail.com> writes:
    I looked on some constructs that helps with iteration over array in
    plpgsql. I propose a following syntax:
    FOR var IN [array variable | array expression]
    LOOP
    I don't have any opinion about whether the functionality proposed here
    is worth the trouble, but I do have an opinion about that syntax: it's
    an awful choice.  plpgsql has enough trouble already distinguishing
    between integer for-loops and query for-loops, not to mention trouble
    in producing a helpful error message when somebody gets either of those
    constructs slightly wrong.  Providing a variant where a single
    expression can follow IN will make both of those problems an order of
    magnitude worse.  As an example, is this a for-in-query or a
    for-in-array?

    FOR v IN (SELECT arraycol FROM tab) LOOP ...
    This is a subquery - so it is a for-in-array - should return one row
    with one column. Similar construct is in SQL/PSM

    where you can to write SET var = (SELECT ...)

    You cannot to write just (SELECT ...) anywhere
    Either answer is plausible depending on whether you assume the
    parentheses make it a subquery.

    Pick something less easily confusable with the existing constructs.
    It's not simple - FOR i IN array is natural - Original ADA use a very
    similar construct.

    FOR i IN ARRAY has problem with constant array - FOR i IN ARRAY ARRAY[1,2,3,]
    and FOREACH is used in Oracle for absolutely different task.

    >

    we have now a for-in-cursor, so there is a precedent.

    regards

    Pavel
    regards, tom lane
  • Tom Lane at Sep 28, 2010 at 8:32 pm

    Pavel Stehule writes:
    2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:
    As an example, is this a for-in-query or a
    for-in-array?

           FOR v IN (SELECT arraycol FROM tab) LOOP ...
    This is a subquery - so it is a for-in-array - should return one row
    with one column.
    That's not obvious at all. It's legal right now to write that, and it
    will be interpreted as for-in-query. Furthermore, there are cases where
    it's essential to be able to write a left paren before SELECT, so that
    you can control the precedence of UNION/INTERSECT/EXCEPT constructs.
    So you're proposing to remove functionality and break existing code in
    order to have a "simple" syntax for for-in-array.

    regards, tom lane
  • Pavel Stehule at Sep 28, 2010 at 8:40 pm

    2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:
    Pavel Stehule <pavel.stehule@gmail.com> writes:
    2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:
    As an example, is this a for-in-query or a
    for-in-array?

    FOR v IN (SELECT arraycol FROM tab) LOOP ...
    This is a subquery - so it is a for-in-array - should return one row
    with one column.
    That's not obvious at all.  It's legal right now to write that, and it
    will be interpreted as for-in-query.
    but it has not a sense. It's based on implementation and I am sure, so
    this isn't documented. Yes, we are able to write

    a := 10 FROM tab WHERE y = 10

    but it is just more bug then required feature.

    FOR v IN (SELECT FROM) when select returns more than one row is big
    inconsistency - and this is bug, when this is allowed

    Regards

    Pavel

    Furthermore, there are cases where
    it's essential to be able to write a left paren before SELECT, so that
    you can control the precedence of UNION/INTERSECT/EXCEPT constructs.
    So you're proposing to remove functionality and break existing code in
    order to have a "simple" syntax for for-in-array.

    regards, tom lane
  • Robert Haas at Sep 28, 2010 at 8:49 pm

    On Tue, Sep 28, 2010 at 4:39 PM, Pavel Stehule wrote:
    2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:
    Pavel Stehule <pavel.stehule@gmail.com> writes:
    2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:
    As an example, is this a for-in-query or a
    for-in-array?

    FOR v IN (SELECT arraycol FROM tab) LOOP ...
    This is a subquery - so it is a for-in-array - should return one row
    with one column.
    That's not obvious at all.  It's legal right now to write that, and it
    will be interpreted as for-in-query.
    but it has not a sense.
    It has a very fine sense. It's completely obvious to me what that
    means, and you're proposing to break it. In a word: no.

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise Postgres Company
  • Andrew Dunstan at Sep 28, 2010 at 10:04 pm

    On 09/28/2010 03:41 PM, Pavel Stehule wrote:
    It's not simple - FOR i IN array is natural - Original ADA use a very
    similar construct.

    No it doesn't. In Ada (Note: not ADA) FOR can only iterate over one
    thing: a discrete subtype (e.g. an integer or enumeration type, or a
    range of it)[1]. You can say:

    for i in my_array'range loop ...

    but that iterates over the array's index, not over its values.And there
    is no ambiguity with other things you might loop over because there
    aren't any.

    cheers

    andrew

    [1]http://www.adaic.org/standards/05rm/html/RM-5-5.html

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedSep 28, '10 at 6:25a
activeSep 29, '10 at 5:20a
posts28
users10
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase