Thread: Help with a plpgsql function

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

>         REVOKE ALL ON my_record.table FROM my_user;

I'd guess you'd need to use EXECUTE here, maybe
EXECUTE ''REVOKE ALL ON '' || my_record.table ||
 '' FROM my_user'';