I want to write a PostgreSQL function that returns a table, plus an additional column. Is there a way to do this without manually specifying RETURNS TABLE (col1 type, col2 type, ...)
?
For example, consider the following function:
CREATE FUNCTION get_users_with_most_videos_since_time(ts TIMESTAMPTZ)
RETURNS SETOF "user" AS $$
SELECT
u.*,
count(v.id) AS vids_since
FROM "user" AS u
INNER JOIN "video" AS v ON v.creator_id = u.id
WHERE v.created_at > ts
GROUP BY u.id
ORDER BY vids_since DESC;
$$ LANGUAGE SQL;
This fails with the error:
ERROR: return type mismatch in function declared to return "user" DETAIL: Final statement returns too many columns.
Fair enough, we're including that vids_since
column, which doesn't exist in the "user"
table.
So to fix this I'd want to change it to something like:
CREATE FUNCTION get_users_with_most_videos_since_time(ts TIMESTAMPTZ)
RETURNS
TABLE (<<< all columns from table "user" >>>, vids_since BIGINT)
AS $$
...
Is there a way to do this without having to re-copy the entire schema of the "user"
table here?
create view user1 as select *, null::int as vids_since from "user" where false;
, then useuser1
as the function result type.