Обсуждение: plpgsql FOR
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)
> 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@debis.com (Jan Wieck) #
Hey all, Readline functionality not working in psql. I've installed PostgreSQL 7.0 RC1 on RedHat 6.2. I have configured using: --with-libraries=/usr/lib which is where the libreadline.so.3 file is. One of the things I've noticed, although I'm not sure of it's significance, is that when the configure routine checks for the readline.h file it returns "no". Any thoughts? Thanks, -=michael=-
"Michael S. Kelly" wrote: >Hey all, > >Readline functionality not working in psql. > >I've installed PostgreSQL 7.0 RC1 on RedHat 6.2. I have configured using: > > --with-libraries=/usr/lib This shouldn't be necessary; it's the standard location. >which is where the libreadline.so.3 file is. One of the things I've >noticed, although I'm not sure of it's significance, is that when the >configure routine checks for the readline.h file it returns "no". This is why you aren't getting readline. Either you don't have the readline development files installed, or you need to specify --with-includes=/path/to/readline.h -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID 32B8FAA1 ======================================== "Submit yourselves therefore to God. Resist the devil, and he will flee from you." James 4:7
Do you have the readline header file installed anywhere on your system (/usr/include perhaps?) If not, you need to install it in the version of readline your system has. On Redhat systems you do this by installing the readline-devel rpm. Yours, Moray ---------------------------------------------------------------- Moray.McConnachie@computing-services.oxford.ac.uk ----- Original Message ----- From: "Michael S. Kelly" <michaelk@axian.com> To: <pgsql-general@postgresql.org> Sent: Wednesday, April 26, 2000 6:21 AM Subject: [GENERAL] Having trouble getting readline functional in psql > Hey all, > > Readline functionality not working in psql. > > I've installed PostgreSQL 7.0 RC1 on RedHat 6.2. I have configured using: > > --with-libraries=/usr/lib > > which is where the libreadline.so.3 file is. One of the things I've > noticed, although I'm not sure of it's significance, is that when the > configure routine checks for the readline.h file it returns "no". > > Any thoughts? > > Thanks, > > -=michael=- > >
<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.