POSTGRES ERROR: INTO used with a command that cannot return data

Question

Am trying to execute the variable t_ord which has statement "REVOKE role_test FROM test" . My requirement is to execute the variable t_ord and store the result msg successfull or fail in the variable ret . But am getting the below error ,

ERROR: INTO used with a command that cannot return data CONTEXT: PL/pgSQL function inline_code_block line 9 at EXECUTE

    DO $$
 declare
  t_ord varchar(500):= NULL;
  cursor_name numeric;
  ret varchar(500):= NULL;
 begin
SELECT 'REVOKE '||'role_test'||' FROM '||'test' INTO STRICT t_ord ;
raise notice 't_ord %',t_ord;
/*
 cursor_name := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(cursor_name, t_ord, DBMS_SQL.V7);
      ret      := DBMS_SQL.EXECUTE(cursor_name);
      DBMS_SQL.CLOSE_CURSOR(cursor_name);
*/
EXECUTE t_ord ; -- > not getting desired results as commented code above , cursor_name should be used which is required later in exception block

EXCEPTION
/*   WHEN err THEN
      RAISE EXCEPTION '%', t_msg;
   WHEN OTHERS THEN
         IF DBMS_SQL.IS_OPEN(cursor_name) THEN
            DBMS_SQL.CLOSE_CURSOR(cursor_name);
         END IF;
    RAISE;
*/
  WHEN SQLSTATE '50001' THEN
      RAISE NOTICE '%', t_msg;
WHEN OTHERS THEN
        IF EXISTS(SELECT * FROM pg_cursors WHERE name = 'cursor_name') THEN
        CLOSE cursor_name;
     END IF;
END;

 end $$;

And , How can i implement cursor_name the same way in oracle so that it can be used in exception block ..

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2018-11-05T12:42:24

This is too long for a comment.

The declaration cursor_name numeric; does not make any sense to me. If you want a cursor (which does not seem to be needed here to begin with) you need to declare it as cursor, not numeric You also never open a cursor to begin with. And DBMS_SQL.PARSE is not a standard Postgres function looks like you are trying to migrate some Oracle code.

You are also referencing a variable t_msg that you did not declare. But the error message of the current error is automatically available in the variable SQLERRM

There is also no (documented) SQLSTATE 50001 in Postgres, so I am unsure which error you want to trap there.

As far as I can tell, your code can be simplified to:

DO $$
declare
  t_ord text;
begin
   -- no need for a SELECT to assign a variable
   t_ord := 'REVOKE role_test FROM test';

   raise notice 't_ord %', t_ord;

   EXECUTE t_ord;
EXCEPTION
  WHEN OTHERS THEN
    RAISE NOTICE '%', SQLERRM;
END;
$$;

You don't really need dynamic SQL to begin with, but I assume to simplified your example. To generate the correct REVOKE statement within a PL/pgSQL block, you should be using format to properly handle identifiers in a SQL statements, e.g. assuming you have a variable named, t_role_name and t_user_name you should use something like:

t_ord := format('REVOKE %I FROM %I', t_role_name, t_user_name);

POSTGRES ERROR: INTO used with a command that cannot return data

1 Answers