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),
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),
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,
On Tuesday, August 28, 2018, Pavel Stehule wrote:
> Hi
>
> 2018-08-29 7:09 GMT+02:00 Shaun Savage :
>
>> I have a table with many years as columns. y1976, y2077, .. , y2019,y2020
>> I want to dynamically return a column from a function.
>
>
> Personally, your design is unahappy - against to ideas
Hi
2018-08-29 7:09 GMT+02:00 Shaun Savage :
> I have a table with many years as columns. y1976, y2077, .. , y2019,y2020
> I want to dynamically return a column from a function.
>
no - it is not possible -
the functions should to return exact same set of columns. Teoretically you
can use SETOF R
On Tuesday, August 28, 2018, Shaun Savage wrote:
> I want to dynamically return a column from a function.
>
Variable object identifiers requires executing dynamic SQL. See:
https://www.postgresql.org/docs/10/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
David J.
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')
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.XX FROM allnames AS t1
JOIN
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.XX FROM allnames AS t1
JOIN
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 havin
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:
http
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')
12 matches
Mail list logo
