Postgres Pro
Downloads
Home   >   mailing lists

Thread: plpgsql FOR LOOP question

From
Justin Banks
Date:
Hello -
    After reading the documentation several times, and looking at
the archives, I'm very confused. The PL/pgSQL documentation states
that :

[<<label>>]
FOR record | row IN select_clause LOOP
   statements
END LOOP;

is valid, and after having (probably mistakenly) thought that a
record/row can be a single item, I wrote :

  FOR lgid IN select gid from groups_acl where login = NEW.login LOOP
    ...<do stuff with lgid>...

This, of course, is a syntax error. After reading the archives, I've
progressed to

<snip>
DECLARE
  rec record;
BEGIN
  FOR select x into rec from groups_acl where login = NEW.login LOOP
    ...<do stuff with rec.gid>...
<snip>


and yet I get a

NOTICE:  plpgsql: ERROR during compile of post_account near line 5
ERROR:  parse error at or near "select"

I'm not quite clear on what line is actually 'line 5', for compilation
purposes, but I've narrowed it down to the select in the FOR line by
process of elimination. Here, by way of pedantism, are all the
statements I'm running through to test :

drop function post_account();
drop trigger post_account on account;
create function post_account () returns OPAQUE as '
  DECLARE
    rec record;
    seq int;
  BEGIN
    FOR select x into rec from groups_acl where login = NEW.login LOOP
      select nextval(''access_aid_seq'') into seq;
      insert into access values(seq, NEW.acid);
      insert into groups_access values(rec.gid, seq);
    END LOOP;
  END;
' LANGUAGE 'plpgsql';

create trigger post_account after insert on account
  for each row execute procedure post_account();
insert into account values (
  99999, 'netco', 'foo', 'foo'
);


which results in a

DROP
DROP
CREATE
CREATE
NOTICE:  plpgsql: ERROR during compile of post_account near line 5
ERROR:  parse error at or near "select"


response. Help? I'm using 7.0beta5 on Irix 6.5.5m, and had no problems
with compiling or installing.

Thanks much,

-justinb

--
Justin Banks - WAM!NET Inc., Eagan MN justinb@wamnet.com
"Sorry I am off-topic, but OpenNT is the 2nd best oxymoron I've ever seen.
Right after Microsoft Works." (Jacek Pliszka in comp.emacs.xemacs)

Re: plpgsql FOR LOOP question

From
Justin Banks
Date:
<courtesy copy emailed to wieck@debis.com>

>>>>> "Jan" == Jan Wieck <wieck@debis.com> writes:

    >> Hello - After reading the documentation several times, and
    >> looking at the archives, I'm very confused. The PL/pgSQL
    >> documentation states that :

    Jan>     Looks like you're confused.

Yup, I was.

    >> FOR lgid IN select gid from groups_acl where login = NEW.login LOOP
    >>   ...<do stuff with lgid>...

    Jan>     Here you have the syntax right, but I assume "lgid" isn't
    Jan> a record or row type variable.

Actually, it wasn't, that's where I was confused. I had thought that
since I knew the datatype I was selecting, declaring a variable of
that type was sufficient. Apparently it's not ;)

    Jan>     This time you messed up the syntax. Write it as

    Jan>     FOR rec IN select * from groups_acl where login =
    Jan> NEW.login LOOP ...  END LOOP;

What I wanted was to be pedantic about the datatype I was selecting
(for no particular reason, I guess), but now I understand that I've
got to have something of the general type record.

Of course, as always happens, right after I mailed to the list, I
figured it out myself.

Thanks.

-justinb

--
Justin Banks - WAM!NET Inc., Eagan MN justinb@wamnet.com
I'd fix it for you, but I don't want to break into your site.