Re: pg/plsql question

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