I have a table defined like so:
create table users (
id serial primary key,
name text,
email text,
);
...and I want to write a function that returns rows of the shape:
(
id integer,
name text,
email text,
some_other_column boolean,
)
I managed to get this working with the code below, but I would rather not re-define the columns from the users table:
create or replace function get_users ()
returns table (
id integer,
name text,
email text,
some_other_column boolean,
) as $$
select users.*, true as some_other_column from users;
$$ language sql;
Is there a way to dynamically create a row type by doing something like this? (postgres complains of a syntax error at users.*):
create or replace function get_users ()
returns table (
users.*,
some_other_column boolean
) as $$
select users.*, true as some_other_column from users;
$$ language sql;
Please note that the following query executed directly works just fine:
select users.*, true as some_other_column from users;
The ultimate goal here is to end up with a function callable like select * from get_users() that returns rows that include both columns from existing tables and additional columns. I do not want the caller to worry about exactly how to call the function.
My assumption is that since I can write simple sql that returns the dynamic rows, I ought to be able to store that sql in the database in some way that preserves the structure of the returned rows.
No. There is currently no way to do exactly that (including pg 16).
SQL is a strictly typed language. When you create a function, the return type has to be declared. To return a set of rows (which you can call with SELECT * FROM srf()):
you can return anonymous records (RETURNS SETOF record). But then you have to provide a column definition list with every call.
you can return a polymorphic (row) type (RETURNS SETOF anyelement). But you have to provide the row type (composite type) as parameter to the function and the row type needs to be registered in the system somehow.
you can use any registered row type explicitly, with RETURNS SETOFrowtype. Side effect is that the function now depends on the row type.
you can define the returned row type ad hoc with RETURNS TABLE (...) - where you can even mix row types (composite types) and simple types. But a simple SELECT * FROM srf() will not decompose nested row types - like Mabu's answer goes to demonstrate.
Related:
It all boils down to this:
Is there a way to dynamically create a row type by doing something like this?
No, there is not. SELECT * FROM ... is going to retrieve the column definition list from system catalogs, where the row type has to be registered before you can call the function this way.
Typically it's best to just spell out the column definition list in a RETURNS TABLE () clause. That avoids dependencies. If you need to register a row type based on an existing table quickly without spelling out its columns, you could create a VIEW - or a TEMPORARY VIEW if it's just for the current session:
CREATE TEMP VIEW v_users_plus AS
SELECT *, NULL::boolean AS some_other_column FROM users;
This registers a row type of the same name (v_users_plus) in the system, like for any other table or view. For a non-temporary function, you'll need a non-temporary row type, obviously.