How do I return a rowtype table plus an extra column from a function?
I have a table defined like this:
create table users (
id serial primary key,
name text,
email text,
);
... and I want to write a function that returns strings of the form:
(
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 override 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 string type by doing something like this? (postgres complains about a syntax error in 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;
Note that the following query, executed directly, works fine:
select users.*, true as some_other_column from users;
The end goal is to end up with a function called as select * from get_users()
that returns rows containing both columns from existing tables and additional columns. I don't want the caller to worry about how to call the function.
My guess is that since I can write a simple sql that returns dynamic rows, I should be able to store that sql in the database in some way that preserves the structure of the rows returned.
source to share
Not. There is currently no way to do this (including pg 10).
SQL is a strongly typed language. When you create a function, the return type must be declared. To return a rowset (which you can call with SELECT * FROM srf()
):
-
you can return anonymous records (
RETURNS SETOF record
). But then you need to provide a list of column definitions on every call. -
you can return polymorphic (string) type (
RETURNS SETOF anyelement
). But you need to specify the string type ( composite type ) as a function parameter, and the string type needs to be registered in the system somehow. -
you can use any registered string type explicitly, with
RETURNS SETOF
rowtype
. A side effect is that the function now depends on the string type. -
you can define the ad hoc string return type with
RETURNS TABLE (...)
- where you can even mix string types ( composite types ) and simple types. But a simpleSELECT * FROM srf()
one will not decompose nested string types - for example, Mabu's answer shows to demonstrate.
on this topic:
It all boils down to this:
Is there a way to dynamically create a string type by doing something like this?
No no. SELECT * FROM ...
going to get a list of column definitions from the system catalogs where the row type needs to be registered before you can call the function this way.
It is generally best to provide a list of column definitions in a sentence RETURNS TABLE ()
. This avoids dependencies. If you need to quickly register a row type based on an existing table without specifying its columns, you can create VIEW
- or 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 the row type with the same name ( v_users_plus
) in the system as it would for any other table or view. For a non-temporary function, you need a non-temporary string type.
source to share
You can think of a table as a pseudo-type, but you have to slightly modify the function and query that calls this function as shown below.
Creature:
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;
Vocation:
SELECT (row_users).*, some_other_column FROM get_users();
Actually you can try a different return type for a dynamic structure like Refcursor or JSON ... It depends on the language or application you are using.
source to share