How to return a value inside a postgreSQL PL/pgSQL EXECUTE

Question

I am trying to return the insert id from within EXECUTE. This works fine:

CREATE OR REPLACE FUNCTION public.mytest2()
    RETURNS INTEGER
LANGUAGE plpgsql
AS $function$
DECLARE
    retval  INTEGER;
    RETURNING "DULid" INTO retval;';
BEGIN
    INSERT INTO "dbUpdateLog" (notes) VALUES ($$hello$$) RETURNING "DULid" INTO retval;
    RETURN retval;
END;
$function$

However, when doing it inside EXECUTE I get an error:

CREATE OR REPLACE FUNCTION public.mytest2()
    RETURNS INTEGER
LANGUAGE plpgsql
AS $function$
DECLARE
    retval  INTEGER;
BEGIN
    EXECUTE 'INSERT INTO "dbUpdateLog" (notes) VALUES ($$hello$$) RETURNING "DULid" INTO retval;';
    RETURN retval;
END;
$function$

I need to use EXECUTE as the queries will be dynamically generated. This is the error:

ERROR:  syntax error at or near "INTO"
LINE 1: ...Log" (notes) VALUES ($$hello$$) RETURNING "DULid" INTO retva...

How can I get a return value (specifically the insert ID in this case) when used inside EXECUTE?

Answer 1

Use execute... into...:

BEGIN
    EXECUTE 'INSERT INTO "dbUpdateLog" (notes) VALUES ($$hello$$) RETURNING "DULid"'  
    INTO retval;
    RETURN retval;
END;

See Executing Dynamic Commands