By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,470 Members | 949 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,470 IT Pros & Developers. It's quick & easy.

Not doing the execute of the return query from a cursor

P: 1
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.
4 Weeks Ago #1
Share this question for a faster answer!
Share on Google+

Post your reply

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