Help with a plpgsql function

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

Responses

Browse pgsql-general by date

  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