plpgsql FOR <select> LOOP question

From: Justin Banks <justinb(at)wamnet(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: plpgsql FOR <select> LOOP question
Date: 2000-04-25 19:22:33
Message-ID: 14597.60623.316612.218717@flotsam.cops.wamnet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)wamnet(dot)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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ed Loehr 2000-04-25 19:43:54 Re:
Previous Message Mike Mascari 2000-04-25 19:20:55 Re: