using a plpgsql function argument as a table column.
I have a table with many years as columns. y1976, y2077, .. ,
y2019,y2020 I want to dynamically return a column from a function.
select * from FUNCTION('y2016') .....
select t1.cola t1.colb, t1.colc, t2.y2016 from ..... Where t2.y2016 != 0;
or if I select year y2012 I want FUNCTION('y2012')
select t1.cola t1.colb, t1.colc, t2.y2012 from ..... Where t2.y2012 != 0;
to generalize
select * from FUNCTION( year_column )
select t1.cola t1.colb, t1.colc, t2.year_column from ..... Where
t2.year_column != 0;
is it possible? if so how?
On Tuesday, August 28, 2018, Shaun Savage <savages@taxnvote.org> wrote:
I want to dynamically return a column from a function.
Variable object identifiers requires executing dynamic SQL. See:
https://www.postgresql.org/docs/10/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
David J.
Hi
2018-08-29 7:09 GMT+02:00 Shaun Savage <savages@taxnvote.org>:
I have a table with many years as columns. y1976, y2077, .. , y2019,y2020
I want to dynamically return a column from a function.
no - it is not possible -
the functions should to return exact same set of columns. Teoretically you
can use SETOF RECORD functions and you can specify result in query like
SELECT * FROM fx('xxx') y(c1, c2, c3, ..)
Personally, your design is unahappy - against to ideas of relations
databases. So any native tools will be impossible.
The best what you can is writing query generator and dynamicly create
queries on client side.
Regards
Pavel
Show quoted text
select * from FUNCTION('y2016') .....
select t1.cola t1.colb, t1.colc, t2.y2016 from ..... Where t2.y2016 != 0;
or if I select year y2012 I want FUNCTION('y2012')
select t1.cola t1.colb, t1.colc, t2.y2012 from ..... Where t2.y2012 != 0;
to generalize
select * from FUNCTION( year_column )
select t1.cola t1.colb, t1.colc, t2.year_column from ..... Where
t2.year_column != 0;is it possible? if so how?
On Tuesday, August 28, 2018, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hi
2018-08-29 7:09 GMT+02:00 Shaun Savage <savages@taxnvote.org>:
I have a table with many years as columns. y1976, y2077, .. , y2019,y2020
I want to dynamically return a column from a function.Personally, your design is unahappy - against to ideas of relations
databases. So any native tools will be impossible.
+1
I don’t know for sure that what is desired is not possible but only because
the specification and model are more than I wish to untangle at the
moment...
David J.