From: | ss <ss(at)tuxclub(dot)org> |
---|---|
To: | Tim Cross <theophilusx(at)gmail(dot)com> |
Cc: | "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: using a plpgsql function argument as a table column. |
Date: | 2018-08-29 05:40:16 |
Message-ID: | a2fd6cb9-b9e1-d00b-f329-7e10892c95aa@tuxclub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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: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(at)tuxclub(dot)org
> <mailto:ss(at)tuxclub(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2018-08-29 05:58:05 | Re: using a plpgsql function argument as a table column. |
Previous Message | Shaun Savage | 2018-08-29 05:38:48 | Re: using a plpgsql function argument as a table column. |