- 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_statement||''''; is not executing the sql showing in sql_new_statement.
