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