I have a list of columns for each entity I want to get from a SELECT/UPDATE/INSERT statement.
I want to reuse the same list instead of having a copy in each query.
SELECT col1, col2 FROM entity; INSERT INTO entity (...) VALUES (...) RETURNING col1, col2; UPDATE entity SET ... RETURNING col1, col2;
It's not always simple
col1, but also more complex expressions e.g.
COALESCE(a, b). That's why I am aiming for reuse.
One way I found it can be done is with functions such as this:
CREATE FUNCTION to_entity_columns( e entity, OUT col1 INTEGER, OUT col2 INTEGER ) AS $$ SELECT e.col1, e.col2 $$ LANGUAGE SQL IMMUTABLE STRICT;
It's possible to do:
SELECT (to_entity_columns(entity)).* FROM entity; INSERT INTO entity (...) VALUES (...) RETURNING (to_entity_columns(entity)).*; UPDATE entity SET ... RETURNING (to_entity_columns(entity)).*;
While this approach works, the query time now scales with a number of rows. This means the time can go up as much as 100x or 1000x. I see queries going from 1ms to 1s. The function is always IMMUTABLE but Postgres won't inline it as I would hope. It is because (see source code here) the function returns a RECORD.
I have tried to modify the function e.g. to return a composite type instead, remove
.* from the function call, but it doesn't make a difference.
The question here is twofold:
a) Is there a way to make the functions like the one above work with reasonable performance?
b) Are there any alternatives that would allow simple reuse of the list of columns like shown in the example above?