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?

Thanks, Michael

Wed, 3 Mar 1999 16:12:54 -0600 
Michael Davis <michael.davis@prevuenet.com>
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) #



Fri, 5 Mar 1999 10:59:48 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] for row in select loop question