Replace some column values in PostgreSQL function returning SETOF tablename

Question

I have a base table with many derived tables in PostgreSQL 9.2. For each derived table there is a function that returns "SETOF derived_table". These functions are generated code and they don't need to know the derived column names, because they simply call

SELECT *
FROM derived_table
WHERE [some conditions on base columns]

I now want the functions to modify the returned values for some of the base columns, but continue to return all the derived columns, ie. something like this:

SELECT 'something else' AS base_col1, base_col2, base_col3, [all derived columns]
FROM derived_table
WHERE ...

Is there a way to express this in Postgres without listing all derived column names?

Alternatively, I could return the data I want in separate columns if only there was a way to define the function to return something like "SETOF my_special_column+derived_table". Again, I want to somehow avoid listing all the derived column names, so the functions can still be easily auto-generated.

Clodoaldo Neto · Accepted Answer · 2014-09-07 20:52:42Z

1

If you create a view of the modified columns

create view view_derived_table as
select 'something else' as new_column, *
from derived_table;

Then you can return a setof view_derived_table

create function f_view_derived_table()
returns setof view_derived_table as $$
    select *
    from view_derived_table
    where some_condition;
$$ language sql;

edited Sep 7, 2014 at 20:52

answered Sep 7, 2014 at 19:42

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

EM0 Over a year ago

An interesting idea! As posted, this still requires me to manually list the derived columns, but if I add a new column instead the view can be defined as: SELECT 'something else' AS new_column, * FROM derived_table

Clodoaldo Neto Over a year ago

@EM Yes, that is what I was my intention but I just copied your code and forgot to change it to , *

Collectives™ on Stack Overflow

Replace some column values in PostgreSQL function returning SETOF tablename

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related