Re: Error while executing a view in postgres 10.5
От | pavan95 |
---|---|
Тема | Re: Error while executing a view in postgres 10.5 |
Дата | |
Msg-id | 1553253964065-0.post@n3.nabble.com обсуждение исходный текст |
Ответы |
Re: Error while executing a view in postgres 10.5
("Jonathan S. Katz" <jonathan.katz@excoventures.com>)
|
Список | pgsql-sql |
Hi Community, The below is the error which I'm encountering while executing the provided script. Script: create or replace view object_privileges as select objtype, schemaname, objname, owner, objuser, privs, string_agg( (case privs_individual when 'arwdDxt' then 'All' when '*' then 'Grant' when 'r' then 'SELECT' when 'w' then 'UPDATE' when 'a' then 'INSERT' when 'd' then 'DELETE' when 'D' then 'TRUNCATE' when 'x' then 'REFERENCES' when 't' then 'TRIGGER' when 'X' then 'EXECUTE' when 'U' then 'USAGE' when 'C' then 'CREATE' when 'c' then 'CONNECT' when 'T' then 'TEMPORARY' else 'Unknown: '||privs end ), ', ' ORDER BY privs_individual) as privileges_pretty from (select objtype, schemaname, objname, owner, privileges, (case when coalesce(objuser,'') is not distinct from '' then 'public' else objuser end) || (case when pr2.rolsuper then '*' else '' end) as objuser, privs, (case when privs in ('*','arwdDxt') then privs else regexp_split_to_table(privs,E'\\s*') end) as privs_individual from (select distinct objtype, schemaname, objname, coalesce(owner,'') || (case when pr.rolsuper then '*' else '' end) as owner, regexp_replace(privileges,E'\/.*','') as privileges, (regexp_split_to_array(regexp_replace(privileges,E'\/.*',''),'='))[1] as objuser, (regexp_split_to_array(regexp_replace(privileges,E'\/.*',''),'='))[2] as privs from (SELECT n.nspname as schemaname, c.relname as objname, CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'S' THEN 'sequence' END as objtype, regexp_split_to_table(array_to_string(c.relacl,','),',') as privileges, pg_catalog.pg_get_userbyid(c.relowner) as Owner FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r', 'v', 'S', 'f') AND n.nspname !~ '(pg_catalog|information_schema)' --AND pg_catalog.pg_table_is_visible(c.oid) /*Uncomment to show only objects */ ) as y /* visible in search path */ left join pg_roles pr on (pr.rolname = y.owner) ) as p2 left join pg_roles pr2 on (pr2.rolname = p2.objuser) --where coalesce(p2.objuser,'') is distinct from '' /*Uncomment to hide "public" role */ ) as p3 group by objtype, schemaname,objname, owner, objuser, privs order by objtype,schemaname,objname,objuser,privileges_pretty; comment on column object_privileges.owner is '"*" after the owner indicates that the owner is a superuser'; comment on column object_privileges.objuser is '"*" after the objuser indicates that the objuser is a superuser'; Error: ERROR: set-returning functions are not allowed in CASE LINE 37: else regexp_split_to_table(privs,E'\... ^ HINT: You might be able to move the set-returning function into a LATERAL FROM item. Could anyone please help me to resolve it. Regards, Pavan -- Sent from: http://www.postgresql-archive.org/PostgreSQL-sql-f2142323.html
В списке pgsql-sql по дате отправления:
Предыдущее
От: Pavel StehuleДата:
Сообщение: Re: Moving from TSQL to PL/pgsql select into a variable...