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@xxxxxxxxxxxxxxx>: > 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
