How can I return an additional column of a table without having to repeat all column names and types in the return definition?

Question

I have table with a lot of columns. I want to create a function which returns all these column, with an additional column. Is there a way to do this type-safe (that is, without returning a record) without having to repeat all column names and types?

For example:

create table t
(
    t1 int,
    t2 int,
    t3 text,
    t4 boolean
);

create function extra_t() returns table(t1 int, t2 int, t3 text, t4 boolean, extra text) as
$$
    select t.*, 'example'::text from t;
$$ language sql

It's quite annoying that I have to repeat t1 int, t2 int, t3 text, t4 boolean in the function definition.

score 2 · Accepted Answer · edited Sep 21 '18 at 21:37

2

I am with you in your struggle, and I don't know of a good way to do this.

That said, this is a hacky way to do this. It does shift the burden of specifying individual fields in the return type to the actual function text, but it does make the whole thing a bit more digestible, in my opinion.

create table t2 (
  extra_text text
) inherits (t);

create or replace function extra_t() returns setof t2 as
$$
    select t.*, 'example'::text from t;
$$ language sql;

Like I said, it's not a good way. It's just a way.

edited Sep 21 '18 at 21:37

Laurenz Albe

209,280
17
206
263

answered Sep 21 '18 at 19:51

Hambone

15,600
8
46
69

IMO using view it could be simpler a bit (at least it does not require to create physical storage for the dummy table) – Abelisto Sep 21 '18 at 21:48
Clever idea. You can make them `UNLOGGED` to prevent @Abelisto's remark. – Katrin Sep 22 '18 at 11:24

How can I return an additional column of a table without having to repeat all column names and types in the return definition?

1 Answers1