While I have accomplished what I needed with the pgedit script given by
John, I am still curious as to why mine is not working...
Here is the latest version:
/* */
create or replace function fred_on_all() RETURNS integer AS '
declare
v_schema varchar;
v_user varchar;
v_t varchar;
begin
v_user := ''user'';
v_schema := ''public'';
FOR v_t in select tablename from pg_catalog.pg_tables where
schemaname = v_schema
LOOP
raise notice ''v_t is %'', t;
END LOOP;
return 1;
end;
' LANGUAGE 'plpgsql';
Please note that all ticks above are single ticks.
Here is what I do to execute it:
excilan=# \i grant.sql
CREATE FUNCTION
excilan=# select fred_on_all();
ERROR: missing ".." at end of SQL expression
CONTEXT: compile of PL/pgSQL function "fred_on_all" near line 8
If anyone could shade some lights...
Much appreciated.
fred
On Tue, 2005-03-15 at 15:35 +0100, Fred Blaise wrote:
> Hello all
>
> I am trying to grant privs to a user on all tables. I think I understood
> there was no command to do that.... :// so I wrote the following:
>
> create or replace function granting() RETURNS integer AS '
> declare
> v_schema varchar;
> v_user varchar;
> begin
> v_user := "user"
> v_schema := "public"
> FOR t in select tablename from pg_tables where schemaname =
> v_schema
> LOOP
> grant select on t to v_user;
> END LOOP;
> return 1;
> end;
> ' LANGUAGE plpgsql;
>
>
> I then login to psql, and do a \i myscript.sql. It returns CREATE
> FUNCTION, but I cannot see anything. The tables are not granted, etc...
> Also I am trying to find out how to debug this. How can I print out to
> STDOUT the value of t for example?
>
> Thanks for any help
>
> Best,
>
> fred
