Return ID of last inserted row in PostgreSQL with RETURNING clause

Question

I've got a pretty simple function defined like so:

CREATE OR REPLACE FUNCTION create_new_order(....) RETURNS integer AS
$BODY$
BEGIN
    PERFORM add_points_to_usage(client_id_p, date_in_p, total_points_p);

INSERT INTO orders (...) VALUES (...)
    RETURNING ident;
END;
$BODY$ LANGUAGE plpgsql;

Where I'm struggling is how to actually RETURN the value stored in the ident field back via the RETURNING clause. I've tried setting the value to a variable but that either doesn't work or I'm just messing up the syntax.

Daniel Vérité · Accepted Answer · 2013-08-06 22:04:49Z

1

You're missing the variable declaration, the INTO clause and the final RETURN:

CREATE OR REPLACE FUNCTION create_new_order(....) RETURNS integer AS
$BODY$
DECLARE
  var_ident int;
BEGIN
    PERFORM add_points_to_usage(client_id_p, date_in_p, total_points_p);
    INSERT INTO orders (...) VALUES (...)
       RETURNING ident INTO var_ident;
    RETURN var_ident;
END;
$BODY$ LANGUAGE plpgsql;

answered Aug 6, 2013 at 22:04

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

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

Collectives™ on Stack Overflow

Return ID of last inserted row in PostgreSQL with RETURNING clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related