Обсуждение: 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?

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?

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@tuxclub.org> 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?

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?

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> 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

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

I fixed it by changed that data. all the years were in one row so I 
created a separate row for each year.

create table tvalues as
select t.key, year, value from values t
   join lateral (values
     (2021,t.y2021),
     (2020,t.y2020),
     (2019,t.y2019),
     (2018,t.y2018),
     (2017,t.y2017),
     (2016,t.y2016),
     (2015,t.y2015),
     (2014,t.y2014),
     (2013,t.y2013),
     (2012,t.y2012),
     (2011,t.y2011),
     (2010,t.y2010),
     (2009,t.y2009),
     (2008,t.y2008),
     (2007,t.y2007),
     (2006,t.y2006),
     (2005,t.y2005),
     (2004,t.y2004),
     (2003,t.y2003),
     (2002,t.y2002),
     (2001,t.y2001),
     (2000,t.y2000),
     (1999,t.y1999),
     (1998,t.y1998),
     (1997,t.y1997),
     (1996,t.y1996),
     (1995,t.y1995),
     (1994,t.y1994),
     (1993,t.y1993),
     (1992,t.y1992),
     (1991,t.y1991),
     (1990,t.y1990),
     (1989,t.y1989),
     (1988,t.y1988),
     (1987,t.y1987),
     (1986,t.y1986),
     (1985,t.y1985),
     (1984,t.y1984),
     (1983,t.y1983),
     (1982,t.y1982),
     (1981,t.y1981),
     (1980,t.y1980),
     (1979,t.y1979),
     (1978,t.y1979),
     (1977,t.y1977),
     (1976,t.y1976))
   s(year, value) ON TRUE;


On 08/29/2018 06:40 AM, Adrian Klaver wrote:
> 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
>>>
>>
>
>

I changed the data from the years in one row to each row has a year

create table tvalues as
select t.key, year, value from values t
   join lateral (values
     (2021,t.y2021),
     (2020,t.y2020),
     (2019,t.y2019),
     (2018,t.y2018),
     (2017,t.y2017),
     (2016,t.y2016),
     (2015,t.y2015),
     (2014,t.y2014),
     (2013,t.y2013),
     (2012,t.y2012),
     (2011,t.y2011),
     (2010,t.y2010),
     (2009,t.y2009),
     (2008,t.y2008),
     (2007,t.y2007),
     (2006,t.y2006),
     (2005,t.y2005),
     (2004,t.y2004),
     (2003,t.y2003),
     (2002,t.y2002),
     (2001,t.y2001),
     (2000,t.y2000),
     (1999,t.y1999),
     (1998,t.y1998),
     (1997,t.y1997),
     (1996,t.y1996),
     (1995,t.y1995),
     (1994,t.y1994),
     (1993,t.y1993),
     (1992,t.y1992),
     (1991,t.y1991),
     (1990,t.y1990),
     (1989,t.y1989),
     (1988,t.y1988),
     (1987,t.y1987),
     (1986,t.y1986),
     (1985,t.y1985),
     (1984,t.y1984),
     (1983,t.y1983),
     (1982,t.y1982),
     (1981,t.y1981),
     (1980,t.y1980),
     (1979,t.y1979),
     (1978,t.y1979),
     (1977,t.y1977),
     (1976,t.y1976))
   s(year, value) ON TRUE;

On 08/29/2018 06:40 AM, Adrian Klaver wrote:
> 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
>>>
>>
>
>