From: | Roberto Andrade Fonseca <randrade(at)abl(dot)com(dot)mx> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Help with a plpgsql function |
Date: | 2002-02-16 21:05:01 |
Message-ID: | 1013893501.1970.4.camel@ingrid.andrade.casa |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)abl(dot)com(dot)mx
From | Date | Subject | |
---|---|---|---|
Next Message | Russell Yanofsky | 2002-02-16 23:01:43 | Re: Can PostgreSQL be used with ASP or .NET? |
Previous Message | Vince Vielhaber | 2002-02-16 18:50:39 | Re: Question: Who\'s Using Postgres |