PostgreSQL using NO_DATA_FOUND exception in a sored Procedure

Viewed 26

When I try to run a PostgreSQL stored procedure including the "STRICT" option and a record variable (rec) as I try to save (run the create statement) I get the following error:

ERROR:  syntax error at or near "rec"
LINE 14:  INTO STRICT rec

I' am following the guidelines stated on the official documentation https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW where the only reference missing is that this can or can't be used inside a function or a stored procedure. I guess there should be a reasonable answer to this issue I couldn't found. I was able to succeed running an equivalent code inside a Do block, so it seams there is some incompatibility declaring a record type variable and functions. This is the function code I was running:

CREATE OR REPLACE FUNCTION test_nodata()
RETURNS TABLE(id int, active boolean)

 LANGUAGE plpgsql
AS $$

DECLARE
 rec record;
 tab VARCHAR ='text_maintenance_news';

    BEGIN
        RETURN QUERY
        SELECT tm.id, tm.active
        INTO STRICT rec
        FROM text_maintenance_news tm
        WHERE tm.active
        LIMIT 1;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
        RAISE 'Query from % has no data', tab; 
END;
$$

As mentioned at the official documents, postgreSQL doesn't consider no data as an error but as a warning that the programmer must handle. Main reason to include the STRICT option.

Do block code (It works!) and result:

DO
LANGUAGE plpgsql
$$
DECLARE
    rec record;
    tab varchar = 'text_maintenance_news';
BEGIN
    SELECT *
    INTO STRICT rec
    FROM text_maintenance_news
    WHERE active
    LIMIT 1;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE 'Query from % return no data', tab;
END;
$$

ERROR:  Query from text_maintenance_news return no data
CONTEXT:  PL/pgSQL function inline_code_block line 13 at RAISE
SQL state: P0001
0 Answers
Related