Re: function to grant select on all tables in several schemas

Hello

I am not sure, but maybe .. you are doesn't use correct quotes:

you use ´´, but you have to use ''

Regards
Pavel Stehule

2010/1/20 Gerd Koenig <koenig(at)transporeon(dot)com>:
> Hello,
>
> I'm looking for a solution to grant select to a group to have "read-only"
> group across all tables/views/.. in several schemas. I already found some
> workarounds and I decided to create a function to cover this topic.
> But calling this function throws the error:
> ""
> ERROR:  column "´r´" does not exist
> LINE 1: ...OM pg_class t, pg_namespace s WHERE t.relkind IN (´r´, ´v´,´...
>                                                             ^
> QUERY:   SELECT pg_catalog.quote_ident(s.nspname) AS schema_name,
> pg_catalog.quote_ident(t.relname) AS relation_name FROM pg_class t,
> pg_namespace s WHERE t.relkind IN (´r´, ´v´,´S´) AND t.relnamespace=s.oid AND
> s.nspname = ´tisys´ order by s.nspname
> CONTEXT:  PL/pgSQL function "grant_select_to_ro_group" line 5 at FOR over
> SELECT rows
> ""
>
> The function was created by:
> ""
> CREATE OR REPLACE FUNCTION grant_select_to_ro_group()
> RETURNS TEXT AS '
> DECLARE
>    sql text;
>    rel record;
> BEGIN
>    FOR rel IN SELECT pg_catalog.quote_ident(s.nspname) AS schema_name,
>                      pg_catalog.quote_ident(t.relname) AS relation_name
>        FROM pg_class t, pg_namespace s
>        WHERE t.relkind IN (´r´, ´v´,´S´)
>        AND t.relnamespace=s.oid AND s.nspname = ´tisys´ order by s.nspname
>    LOOP sql := ''GRANT SELECT ON '' || rel.schema_name || ''.'' ||
> rel.relation_name || '' TO ro_group'';
>        RAISE NOTICE ''%'', sql;
>        EXECUTE sql;
>    END LOOP;
>    RETURN ''OK'';
> END;
> ' LANGUAGE 'plpgsql';
> COMMENT ON FUNCTION grant_select_to_ro_group()
> IS 'Give select privilege ON all relations in the given schema TO ro_group.';
> ""
>
> ...and has been called by:
> ""
> select grant_select_to_ro_group();
> ""
>
> any hints appreciated......GERD....
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>