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

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