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?

klin · Accepted Answer · 2016-10-03 23:52:09Z

13

Use execute... into...:

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

See Executing Dynamic Commands

answered Oct 3, 2016 at 23:52

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Duncklee Over a year ago

Ah what a single misplaced quote will do. Thank you!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related