469,086 Members | 1,182 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,086 developers. It's quick & easy.

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 1893

Post your reply

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

Similar topics

1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.