for row in select loop question

The following function does not work:

CREATE FUNCTION InstallPermissions(varchar, varchar) RETURNS int2 AS '
DECLARE
options ALIAS FOR $1;
username ALIAS FOR $2;
BEGIN
FOR row IN select * from pg_tables LOOP
REVOKE ALL ON row.tablename FROM username;
GRANT options on row.tablename TO username;
END LOOP;
return 0;
END; ' LANGUAGE 'plpgsql';

"select InstallPermissions('test1', 'test2);" returns an error at "select".
Any suggestions on how to eliminate the error?

First you forgot to declare 'row'. Could be type record.

Second REVOKE and GRANT are utility statements not supported
for prepared SPI plans and thus PL/pgSQL currently cannot
execute them.

Jan

--

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

Thanks. Declaring row as a record did the trick. I have a lot of tables
and I am looking to more deeply understand the PostgreSQL security module.

-----Original Message-----
From: jwieck@debis.com [SMTP:jwieck@debis.com]
Sent: Friday, March 05, 1999 3:00 AM
To: Michael Davis
Cc: hackers@postgreSQL.org
Subject: Re: [HACKERS] for row in select loop question

The following function does not work:

CREATE FUNCTION InstallPermissions(varchar, varchar) RETURNS int2

AS '

DECLARE
options ALIAS FOR $1;
username ALIAS FOR $2;
BEGIN
FOR row IN select * from pg_tables LOOP
REVOKE ALL ON row.tablename FROM username;
GRANT options on row.tablename TO username;
END LOOP;
return 0;
END; ' LANGUAGE 'plpgsql';

"select InstallPermissions('test1', 'test2);" returns an error at

"select".

Any suggestions on how to eliminate the error?

First you forgot to declare 'row'. Could be type record.

Second REVOKE and GRANT are utility statements not supported
for prepared SPI plans and thus PL/pgSQL currently cannot
execute them.

Jan

--

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

This is another example of why not allowing utility functions in SPI would
be a Bad Thing.

For what it's worth, I found another case in libpq where you can get a T
message without a D that my utility patch needs to handle. I have attached
the updated patch against the 6.4.2 version of
src/interfaces/libpq/fe-exec.c

Jerry

Older patch reversed out, and new patch applied.

Thanks.

[Charset iso-8859-1 unsupported, filtering to ASCII...]

This is another example of why not allowing utility functions in SPI would
be a Bad Thing.

For what it's worth, I found another case in libpq where you can get a T
message without a D that my utility patch needs to handle. I have attached
the updated patch against the 6.4.2 version of
src/interfaces/libpq/fe-exec.c

Jerry

-----Original Message-----
From: jwieck@debis.com [SMTP:jwieck@debis.com]
Sent: Friday, March 05, 1999 3:00 AM
To: Michael Davis
Cc: hackers@postgreSQL.org
Subject: Re: [HACKERS] for row in select loop question

Second REVOKE and GRANT are utility statements not supported
for prepared SPI plans and thus PL/pgSQL currently cannot
execute them.

Jan

--

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

[Attachment, skipping...]

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026