471,873 Members | 2,041 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,873 software developers and data experts.

Not doing the execute of the return query from a cursor

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE function  user_drop (p_user   in   text)
  2. returns setof record  AS $BODY$ 
  4.    sql_new_statement text;
  5.     cur_user_roles CURSOR(p_user  text)
  6.       for    select 'revoke '||c.rolname||' from '||a.rolname||';'
  7.               from pg_catalog.pg_roles a
  8.               inner join pg_auth_members b on a.oid=b.member
  9.               inner join pg_roles c on b.roleid=c.oid 
  10.               where a.rolname = p_user;
  11.     BEGIN
  12.        open  cur_user_roles(p_user);
  13.        loop
  14.        FETCH cur_user_roles into  sql_new_statement;
  15.         raise notice 'Value: %', sql_new_statement;
  16.         return query execute ''''||sql_new_statement||'''';
  17.         raise notice 'Value: %', return_query;
  18.        EXIT WHEN NOT FOUND;
  19.        raise notice 'Value: %', sql_new_statement;
  20.          commit;
  21.     END LOOP;
  22.    CLOSE cur_user_roles;
  23. return;
  24. exception
  25.    when others then
  26.     null;
  27. END;
  28. $BODY$ LANGUAGE plpgsql;

The return query execute ''''||sql_new_statement||''''; is not executing the sql showing in sql_new_statement.
Feb 27 '20 #1
0 1994

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.