agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: pavan95 <pavan.postgresdba@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Error while executing a view in postgres 10.5
Date: Fri, 22 Mar 2019 04:26:04 -0700 (MST)
Message-ID: <1553253964065-0.post@n3.nabble.com> (raw)

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

view thread (2+ messages)  latest in thread

Message-ID: <1553253964065-0.post@n3.nabble.com>
Permalink:  ../1553253964065-0.post@n3.nabble.com/
Also on:    postgresql.org/message-id/1553253964065-0.post@n3.nabble.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: pavan.postgresdba@gmail.com
  Subject: Re: Error while executing a view in postgres 10.5
  In-Reply-To: <1553253964065-0.post@n3.nabble.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox