How do I return the result of a RETURNING clause from a plpgsql function?

Question

I would like to have a fairly generic function that takes an INSERT, UPDATE, or DELETE, which might contain a RETURNING clause, and return that result. A fairly contrived example might be something like this:

CREATE FUNCTION my_insert(sql_insert TEXT) RETURNS record AS $$
DECLARE
    result record;
BEGIN
    EXECUTE sql_insert INTO result;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

SELECT my_insert('INSERT INTO foo VALUES (1) RETURNING some_column, another_column');

While this works ok, I don't think that record is the right type here, because RETURNING typically returns a table type. I'd like to return the exact same format from my function as RETURNING does, but I'm not sure how.

There's no polymorphic return for functions. Related: [PostgreSQL: How to return dynamic rows from table without using “column definition list”?](http://stackoverflow.com/questions/22861143) — Daniel Vérité, Sep 05 '14 at 16:23
My `RETURNING` clauses will almost always be the same, so I can probably define a composite type and return it in most cases. — Joe Shaw, Sep 05 '14 at 17:16
this codding style is antipattern. You strongly increase a number of dynamic SQL, what is simply wrong (security, debugging, static analyse, performance aspects). When you are write stored procedures, you still have to thinking in the sets, and you should not to encapsulate SQL engine. Dynamic SQL should be used only when there is not possible any any other solution, or when it save thousands rows or in very special cases (but it should be a exceptions). — Pavel Stehule, Sep 12 '14 at 04:49

score 3 · Accepted Answer · answered Sep 05 '14 at 17:20

Returning record is fine, as long as the SELECT from the function defines the table structure:

SELECT * FROM my_insert('INSERT INTO foo VALUES (1) RETURNING some_column, another_column)
    AS f(some_column bigint, another_column timestamp);

If the return value from the my_insert function is always going to be of a certain type, a composite type is maybe a better way to go:

CREATE TYPE my_insert_result AS (
    some_column bigint,
    another_column timestamp
);

And then change the function to return that type:

CREATE FUNCTION my_insert(sql_insert TEXT) RETURNS my_insert_result AS $$
DECLARE
    result my_insert_result;
BEGIN
    EXECUTE sql_insert INTO result;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM my_insert('INSERT INTO foo VALUES (1) RETURNING some_column, another_column');

How do I return the result of a RETURNING clause from a plpgsql function?

1 Answers1