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

On 08/28/2018 10:40 PM, ss wrote:
> 
> 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

So something along lines of:

DO $$
     DECLARE
         col_name varchar;
         out_str  varchar;
     BEGIN
         col_name = 'y'||'2018';
         out_str = format('SELECT %I FROM some_table where %I = 0', 
col_name, col_name );
         RAISE NOTICE '%', out_str;
     END;
$$ LANGUAGE plpgsql;


NOTICE:  SELECT y2018 FROM some_table where y2018 = 0

> 
> Would something like 't2.'||(year)|| work?
> 
> On 08/28/2018 10:37 PM, Tim Cross wrote:
>> Off the top of my head, I think you could do this using dynamic 
>> (execute) SQL in a function. However, it is going to be messy, 
>> possibly slow and likely fragile. You would need to query the 
>> catalogue to get the column names in the table and then build the SQL 
>> dynamically 'on the fly'.
>>
>> Without having more detail, my spider sense tells me you have the 
>> wrong table/relationship design. While you may be able to get it to 
>> work, it is likely you will run into constant problems and additional 
>> complexity that could be avoided with a different design. You really 
>> want a design where your queries are driven by the data in your tables 
>> and not by the names of columns. I would seriously consider 
>> re-examining your schema design, look at how your design fits in with 
>> the normal forms and adapt as necessary.
>>
>> Tim
>>
>> On Wed, 29 Aug 2018 at 15:10, ss <ss@tuxclub.org 
>> <mailto:ss@tuxclub.org>> wrote:
>>
>>     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?
>>
>>
>>
>>
>> -- 
>> regards,
>>
>> Tim
>>
>> --
>> Tim Cross
>>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com