0
votes

I have stored proc in Redshift in plpgsql. When I run call test3() I get exception Amazon Invalid operation: Row Count: 1; 1 statement failed.

CREATE OR REPLACE  PROCEDURE test3()
LANGUAGE plpgsql
AS $$
DECLARE
     found_record RECORD;
     integer_var integer;
BEGIN
    SELECT * INTO  found_record FROM tbl WHERE id='ABC';
IF FOUND THEN 
    GET DIAGNOSTICS integer_var = ROW_COUNT;
    RAISE EXCEPTION 'Row Count: %', integer_var;
END IF;
END;
$$ 

But when I run call test4() I get

0 rows affected CALL executed successfully

CREATE OR REPLACE  PROCEDURE test4()
LANGUAGE plpgsql
AS $$
DECLARE
     found_record RECORD;
     integer_var integer;
BEGIN

    EXECUTE  'SELECT *  FROM tbl WHERE id=\'ABC\'' into found_record ;
IF FOUND THEN 
    GET DIAGNOSTICS integer_var = ROW_COUNT;
    RAISE EXCEPTION 'Row Count: %', integer_var;
END IF;
END;
$$

How do I access FOUND while executing dynamic query

2

2 Answers

1
votes

Dynamic SQL has not a impact on FOUND variable. But you can use a GET DIAGNOSTICS statement. You do it in your example:

CREATE OR REPLACE  PROCEDURE test4()
LANGUAGE plpgsql
AS $$
DECLARE
   found_record RECORD;
   integer_var integer;
BEGIN
  EXECUTE  'SELECT *  FROM tbl WHERE id=\'ABC\'' into found_record ;
  GET DIAGNOSTICS integer_var = ROW_COUNT;
  RAISE EXCEPTION 'Row Count: %', integer_var;
END;
$$

You can use GET DIAGNOSTICS statement everywhere - not only when FOUND is true.

1
votes

You can't, however you can check that found_record is not null.