Thread: using a plpgsql function argument as a table column.

using a plpgsql function argument as a table column.

From

Shaun Savage

Date:

29 August 2018, 11:09:03

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?

Re: using a plpgsql function argument as a table column.

From

"David G. Johnston"

Date:

29 August 2018, 11:58:05

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.

Re: using a plpgsql function argument as a table column.

From

Pavel Stehule

Date:

29 August 2018, 11:58:39

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

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?

Re: using a plpgsql function argument as a table column.

From

"David G. Johnston"

Date:

29 August 2018, 12:03:54

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.

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.