Re: plpgsql FOR <select> LOOP question

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

Looks like you're confused.

>
> [<<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>...

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

>
> 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>

This time you messed up the syntax. Write it as

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

> 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';

BTW

seq := nextval(''access_aid_seq'');

might look more readable.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck(at)debis(dot)com (Jan Wieck) #