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