Hello

Now, statements EXECUTE INTO and SELECT INTO allow using list of scalars.
FORe and FORs allow only ROW o RECORD VARIABLE. I'll plan and I did it
enhance this stmts:

<for> := FOR <target> IN {SELECT | EXECUTE} ... LOOP
<target> := {row|record|comma separated list of scalar vars}

<assign> := <target2> ':=' <expression>
<target2> := {row|record|variable|'ROW(' comma separated list of scalar vars
')'}

for example:
CREATE OR REPLACE FUNCTION test(OUT _rc, OUT _x varchar, OUT _y varchar)
RETURNS SETOF RECORD AS $$
DECLARE _r RECORD;
BEGIN
rc := 0;
-- old style;
FOR _r IN SELECT generate_series AS x, generateseries + 1 AS y FROM
generate_series(1,4) LOOP
_rc := _rc + 1; _x := _r.x; _y := _r.y;
RETURN NEXT;
END LOOP;
-- new one
FOR _x,_y IN SELECT generate_series, generateseries + 1 FROM
generate_series(1,4) LOOP
_rc := _rc + 1;
RETURN NEXT;
END LOOP;
-- new two
FOR _r IN SELECT generate_series AS x, generateseries + 1 AS y FROM
generate_series(1,4) LOOP
_rc := _rc + 1; ROW(_x,_y) := _r;
RETURN NEXT;
END LOOP;
RETURN;
END; $$ LANGUAGE plpgsql;

any comments?
Regards
Pavel Stehule

_________________________________________________________________
Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/

Search Discussions

  • Tom Lane at Dec 22, 2005 at 5:00 pm

    "Pavel Stehule" <pavel.stehule@hotmail.com> writes:
    <for> := FOR <target> IN {SELECT | EXECUTE} ... LOOP
    <target> := {row|record|comma separated list of scalar vars}
    This part seems all right to me.
    <assign> := <target2> ':=' <expression>
    <target2> := {row|record|variable|'ROW(' comma separated list of scalar vars
    ')'}
    As I already said on -patches, I consider this a bad idea. It's too
    error prone (because there's no easy way of seeing what the field order
    will be). And it doesn't add anything that you can't do now. I think
    a series of "var = rec.field" assignments is a preferable way to do it.

    regards, tom lane
  • David Fetter at Dec 22, 2005 at 9:06 pm

    On Thu, Dec 22, 2005 at 10:18:16AM +0100, Pavel Stehule wrote:
    Hello

    Now, statements EXECUTE INTO and SELECT INTO allow using list of scalars.
    FORe and FORs allow only ROW o RECORD VARIABLE. I'll plan and I did it
    enhance this stmts:

    <for> := FOR <target> IN {SELECT | EXECUTE} ... LOOP
    <target> := {row|record|comma separated list of scalar vars}

    <assign> := <target2> ':=' <expression>
    <target2> := {row|record|variable|'ROW(' comma separated list of scalar
    vars ')'}
    How about:

    <target2> := {row|record|variable|'[ROW](' comma separated list of scalar vars ')'}

    instead, where the ROW is optional?

    Cheers,
    D
    --
    David Fetter david@fetter.org http://fetter.org/
    phone: +1 415 235 3778

    Remember to vote!
  • Tom Lane at Dec 22, 2005 at 11:29 pm

    David Fetter writes:
    How about:
    <target2> := {row|record|variable|'[ROW](' comma separated list of scalar vars ')'}
    instead, where the ROW is optional?
    If we're going to do this at all (which I'm still agin), I think the ROW
    keyword is important to minimize ambiguity. If you are allowed to start
    a statement with just "(x, ..." then there will be way too many
    situations where the parser gets confused by slightly bad input,
    resulting in way-off-base syntax error reports. Or worse, no syntax
    error, but a function that does something else than you expected.

    I know that ROW is optional in the bit of SQL syntax that this proposal
    is based on, but that's only because the SQL spec says we have to, not
    because it's a good idea.

    regards, tom lane
  • Andrew Dunstan at Dec 22, 2005 at 11:53 pm

    Tom Lane wrote:
    David Fetter <david@fetter.org> writes:

    How about:
    <target2> := {row|record|variable|'[ROW](' comma separated list of scalar vars ')'}
    instead, where the ROW is optional?
    If we're going to do this at all (which I'm still agin), I think the ROW
    keyword is important to minimize ambiguity. If you are allowed to start
    a statement with just "(x, ..." then there will be way too many
    situations where the parser gets confused by slightly bad input,
    resulting in way-off-base syntax error reports. Or worse, no syntax
    error, but a function that does something else than you expected.

    I know that ROW is optional in the bit of SQL syntax that this proposal
    is based on, but that's only because the SQL spec says we have to, not
    because it's a good idea.


    I see no virtue in this either. It strikes me as just more syntactic
    sugar, and unless I am misreading or out of date it would be another
    incompatibility with Oracle. I don't mind doing that, but I think it
    should be for a better reason than that it accords with someone's taste
    in syntactic style. I'd be somewhat more persuaded if Oracle did this. I
    also agree with Tom's comments about requiring ROW. As I observed
    regarding another syntax proposal, terseness is not always good, and
    redundancy is not always bad.

    cheers

    andrew

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedDec 22, '05 at 9:18a
activeDec 22, '05 at 11:53p
posts5
users4
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase