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

[Shaun Savage <savages@xxxxxxxxxxxx>]

CREATE OR REPLACE FUNCTION test(year VARCHAR)
RETURNS TABLE (agencycode INT, bureaucode INT, acctname VARCHAR, beacat VARCHAR, onoffbudget VARCHAR, val INT)
AS $$
BEGIN
RETURN QUERY SELECT t1.agencycode, t1.bureaucode, t1.acctcode, t2.beacat, t2.onoffbudget, t2.XXXXXX  FROM allnames AS t1
JOIN total AS t2 on t1.agencycode = t2.agencycode and t1.bureaucode = t2.bureaucode and t1.acctcode = t2.acctcode
WHERE t2.XXXXXXX != 0  ORDER BY t1.agencycode, t1.bureaucode, t1.acctcode;
END; $$
LANGUAGE 'plpgsql';

I want to replace XXXXXXX with the argument year.
I hard coded the XXXXXX with 'y2016' and I get back a table

Would something like 't2.'||(year)|| work?

On 08/28/2018 10:24 PM, Thomas Boussekey wrote:

Hello,

Yes, you can :-)

Using a functional that returns a table, like in the following example: http://www.postgresqltutorial.com/plpgsql-function-returns-a-table/

You will have to generate a dynamic sql statement for querying your table with the accurate WHERE clause. Like in this example: https://stackoverflow.com/questions/12780275/dynamic-sql-query-in-postgres

Hope this helps 

Have a nice day 

Thomas 

Le mer. 29 août 2018 à 07:11, ss <ss@xxxxxxxxxxx> a écrit :

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?