Hi:
I'm trying to build a pgsql function that can revoke all privileges of
all the tables of a database, from PUBLIC.
This is the code:
-----------------------------
CREATE OR REPLACE FUNCTION privilegios(varchar) RETURNS bool AS '
DECLARE
my_user ALIAS FOR $1;
my_record RECORD;
BEGIN
FOR my_record IN SELECT c.relname as table FROM pg_class c LEFT JOIN
pg_user u ON c.relowner = u.usesysid WHERE c.relkind IN (''r'','''') AND
c.relname !~ ''^pg_'' ORDER BY 1 LOOP
-- I use the next two lines just to know what is happening
RAISE NOTICE ''Table: %'', my_record.table;
raise notice ''REVOKE ALL ON % FROM %'', my_record.table,
my_user;
REVOKE ALL ON my_record.table FROM my_user;
END LOOP;
RETURN ''t'';
END;
' LANGUAGE 'plpgsql';
-----------------------------------
If I compile and execute the function, typing:
select privilegios('PUBLIC');
I get what follows:
-------------------------
tutorial=# select privilegios('PUBLIC');
NOTICE: Table: asistente
NOTICE: REVOKE ALL ON asistente FROM PUBLIC
NOTICE: Error occurred while executing PL/pgSQL function privilegios
NOTICE: line 11 at SQL statement
ERROR: parser: parse error at or near "$1"
---------------------------------------------
If I comment out the line
REVOKE ALL ON my_record.table FROM my_user;
I don't get any error, but surely nothings interesting happens!.
Can't I GRANT or REVOKE privileges whitin plpgsql?
What am I doing wrong?
--
Saludos,
Roberto Andrade Fonseca
randrade@abl.com.mx
