Function with Pivot (crosstab) on unknown rows postgressql

Question

I have a table similar to this: (Postgressql v.10.8)

id  ref     street      city    total   year
1   2077    Burban Str. London  10000   2010
2   2077    Burban Str. London  12000   2011
3   2077    Burban Str. London  14000   2012
4   2077    Burban Str. London  14000   2013
5   2077    Burban Str. London  10000   2014
6   2077    Burban Str. London  19000   2015
7   2077    Burban Str. London  20000   2016
8   2077    Burban Str. London  10000   2017
9   2077    Burban Str. London  20000   2018
10  2077    Burban Str. London  11000   2019
11  2077    Burban Str. London  13000   2020
12  1000    Ocean road  London  9000    2018
13  1000    Ocean road  London  10000   2019
14  1000    Ocean road  London  12000   2020
15  2000    City Str.   Manchester  500 2019
16  2000    City Str.   Manchester  800 2020

With this table i want to create a function with ref and year as parameters.

If i call the function like this select * from myfunction(2077,2020) i want to get the following output:

ref    street        city     2020    2019     difference
2077   Burban Str.   London   13000   11000    2000

As you can see i want to compare the year 2020 (from parameter) with the year before and also pivot the query so that the years become column with the total as values.

This is as far i come with my knowledge.. with this query i almost get the right output but i don't how to solve the problem with the years :( Do i need to go and hardcode like 50 years here to cover up all possible years or is there i way to use my parameter here since the year is unknown?

select * from crosstab('select ref,street,city,year,total from mytable order by 1,4',$$values ('2010'::text),('2011'::text),('2012'::text),('2013'::text),('2014'::text),('2015'::text),('2016'::text),('2017'::text),('2018'::text),('2019'::text),('2020'::text)$$)
as ct  (ref int,street text,city text,"2010" int,"2011" int,"2012" int,"2013" int,"2014" int,"2015" int,"2016" int,"2017" int,"2018" int,"2019" int,"2020" int);

Demo

So if send parameters ref 2077 and year 2020 the query has to be something like this:

select * from crosstab('select ref,street,city,year,total from mytable order by 1,4',$$values ('2019'::text),('2020'::text)$$)
    as ct  (ref int,street text,city text,"2019" int,"2020" int);

Answer 1

Naming dynamically the columns of a query's result in postgres is not obvious. Here is a full-dynamic solution based on a user-defined composite type and the standard jsonb function jsonb_populate_record :

First we create dynamically the composite type which correspond to a specific year with a procedure :

CREATE OR REPLACE PROCEDURE composite_type(_year integer)
LANGUAGE plpgsql AS
$$
BEGIN
  EXECUTE 'DROP TYPE IF EXISTS ' || quote_ident(_year :: text) ; 
  EXECUTE 'CREATE TYPE ' || quote_ident(_year :: text) || ' AS (ref integer, street text, city text, ' || quote_ident(_year :: text) || ' integer, ' || quote_ident((_year - 1) :: text) || ' integer, difference integer)' ;
END ;
$$ ;

Then we create the generic function test and which implements a dynamic query :

CREATE OR REPLACE FUNCTION test(_ref integer, _year integer)
RETURNS record LANGUAGE plpgsql AS
$$
DECLARE
   row record ;
BEGIN
   EXECUTE 
   'SELECT ref, street, city
         , (array_agg(total ORDER BY year))[2] AS ' || quote_ident(_year :: text) || '
         , (array_agg(total ORDER BY year))[1] AS ' || quote_ident((_year - 1) :: text) || '
         , (array_agg(total ORDER BY year))[2] - (array_agg(total ORDER BY year))[1] AS difference
      FROM mytable
     WHERE ref = ' || quote_nullable(_ref :: text) || '
       AND year BETWEEN ' || quote_nullable((_year - 1) :: text) || ' AND ' || quote_nullable(_year :: text) || '
     GROUP BY ref, street, city' INTO row ;
   RETURN row ;
END ;
$$ ;

Finally, we can call the procedure composite_type and then the function test with the expected input parameters year and ref :

CALL composite_type(2020) ;
SELECT (jsonb_populate_record(NULL :: "2020", to_jsonb(test(2077, 2020)))).* ;

see the result in dbfiddle.