Not doing the execute of the return query from a cursor

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • efmdba
    New Member
    • Feb 2020
    • 1

    Not doing the execute of the return query from a cursor

    Code:
    CREATE OR REPLACE function  user_drop (p_user   in   text)
    returns setof record  AS $BODY$ 
    DECLARE
       sql_new_statement text;
        cur_user_roles CURSOR(p_user  text)
          for    select 'revoke '||c.rolname||' from '||a.rolname||';'
                  from pg_catalog.pg_roles a
                  inner join pg_auth_members b on a.oid=b.member
                  inner join pg_roles c on b.roleid=c.oid 
                  where a.rolname = p_user;
        BEGIN
           open  cur_user_roles(p_user);
           loop
           FETCH cur_user_roles into  sql_new_statement;
            raise notice 'Value: %', sql_new_statement;
            return query execute ''''||sql_new_statement||'''';
            raise notice 'Value: %', return_query;
           EXIT WHEN NOT FOUND;
           raise notice 'Value: %', sql_new_statement;
             commit;
        END LOOP;
       CLOSE cur_user_roles;
    return;
    exception
       when others then
        null;
    END;
    $BODY$ LANGUAGE plpgsql;


    The return query execute ''''||sql_new_s tatement||''''; is not executing the sql showing in sql_new_stateme nt.
    Last edited by gits; Feb 28 '20, 07:54 AM. Reason: added code tags
Working...