How to return a table's rowtype plus an additional column from a function?

Question

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.

Radke

Answer 1

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.

  • Refactor a PL/pgSQL function to return the output of various SELECT queries

• 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:

• Return SETOF rows from PostgreSQL function

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.

Sociable

Answer 2

You can treat the table as pseudo type but you must have a little bit change in the function and the query calling this function as below.

Creating:

create or replace function get_users () 
returns table (
  row_users users, 
  some_other_column boolean
) as $$
    select t, true as some_other_column from users as t;
$$ language sql;

Calling:

SELECT (row_users).*, some_other_column FROM get_users();

In fact, you can try another return type for dynamic structure such as Refcursor or JSON ... It is depend on the language or the application you use.

Requite

This is a great workaround, but my goal here is to have the caller only have to write select * from get_users(). The DB provides a whole set of functions that I'd like to keep consistent. Maybe there's a way to use views? – Radke

I think it wouldn't work. Of course, if you call your function without any argument (same as the example above), you can create a view as CREATE VIEW view_some_thing AS SELECT (row_users).*, some_other_column FROM get_users();. But when you update structure of table users, you must recreate the view. Because when you create a view, database will store the structure of the view into system table, if you change the structure, you must update it too. If you just add some column, it will not raise error, otherwise if you change the column name or type, database will raise exception. – Requite