1) When I run the function below I get an error at "select". If I put the
select statement in single quotes, I get an error at "loop". Any
suggestions how to fix this?
2) When I install the following function:
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';
I get a dir listing displayed in the psql window between each statement.
See the output below. It makes the output verbose and difficult to read.
This happens when installing the function, not when running the function.
Any idea what could be causing this and how to fix it? Here is an example
of the output (scaled back for brevity, the list of files is quite a bit
larger):
mp=> CREATE FUNCTION InstallPermissions(varchar, varchar) RETURNS int2 AS '
mp'> DECLARE
mp'> .Xdefaults .bash_logout .bashrc
.screenrc ...
mp'> options ALIAS FOR $1;
mp'> .Xdefaults .bash_logout .bashrc
.screenrc ...
mp'> username ALIAS FOR $2;
mp'> BEGIN
mp'> FOR row IN ''select * from pg_tables'' LOOP
mp'> .Xdefaults .bash_logout .bashrc
.screenrc ...
mp'> .Xdefaults .bash_logout .bashrc
.screenrc ...
mp'> .Xdefaults .bash_logout .bashrc
.screenrc ...
mp'> REVOKE ALL ON row.tablename FROM username;
mp'> .Xdefaults .bash_logout .bashrc
.screenrc ...
mp'> .Xdefaults .bash_logout .bashrc
.screenrc ...
mp'> .Xdefaults .bash_logout .bashrc
.screenrc ...
mp'> GRANT options on row.tablename TO username;
mp'> .Xdefaults .bash_logout .bashrc
.screenrc ...
mp'> END LOOP;
mp'> .Xdefaults .bash_logout .bashrc
.screenrc ...
mp'> return 0;
mp'> END; ' LANGUAGE 'plpgsql';
CREATE
Thanks, Michael
