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.
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');
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments