How to use PL/pgSQL to construct a table with dynamic columns

Question

I have a Postgres tabled called locations. It has a few million rows with data in the following format

 id |  location_a  |  location_b
----+--------------+--------------
 36 | Sydney       | London
 37 | Atlanta      | London
 38 | New York     | Tokyo
 39 | Tokyo        | Sydney
 40 | Tokyo        | Sydney
.....

I want to be able to generate a pivot table / count of the following form -

The issue is that the number of columns is variable, so it has to be programmatically / dynamically determined instead of using a static SELECT query.

I understand the basic concept of PL/pgSQL in that it is a scripting language that will let me do dynamic things like this.

But I'm having a lot of trouble getting started. Is there an easy way to compute the above?

Answer 1

You can dynamically create a view. The idea and the solution for a simpler case than yours is described in this answer. Please read it before continuing.

We will use the following query to create a view:

with all_locations(location) as (
    select distinct location_a
    from locations
    union
    select distinct location_b
    from locations
)

select location_a as location, json_object_agg(location_b, count order by location_b) as data
from (
    select a.location as location_a, b.location as location_b, count(l.*)
    from all_locations a
    cross join all_locations b
    left join locations l on location_a = a.location and location_b = b.location
    group by 1, 2
    ) s
group by 1
order by 1;

Results:

 location |                                    data                                    
----------+----------------------------------------------------------------------------
 Atlanta  | { "Atlanta" : 0, "London" : 1, "New York" : 0, "Sydney" : 0, "Tokyo" : 0 }
 London   | { "Atlanta" : 0, "London" : 0, "New York" : 0, "Sydney" : 0, "Tokyo" : 0 }
 New York | { "Atlanta" : 0, "London" : 0, "New York" : 0, "Sydney" : 0, "Tokyo" : 1 }
 Sydney   | { "Atlanta" : 0, "London" : 1, "New York" : 0, "Sydney" : 0, "Tokyo" : 0 }
 Tokyo    | { "Atlanta" : 0, "London" : 0, "New York" : 0, "Sydney" : 2, "Tokyo" : 0 }
(5 rows)

The list of cities will be used twice inside the function, so it is stored in the array cities. Note, that you can replace the first query in the function to a simpler one (it is simply ordered list of distinct cities).

create or replace function create_locations_view()
returns void language plpgsql as $$
declare
    cities text[];
    list text;
begin
--  fill array with all cities in alphabetical order
    select array_agg(location_a order by location_a)
    from (
        select distinct location_a
        from locations
        union
        select distinct location_b
        from locations
        ) s
    into cities;

--  construct list of columns to use in select list
    select string_agg(format($s$data->>'%1$s' "%1$s"$s$, city), ', ')
    from unnest(cities) city
    into list;

--  create view from select based on the above list
    execute format($ex$
        drop view if exists locations_view;
        create view locations_view as 
        select location, %1$s
        from (
            select location_a as location, json_object_agg(location_b, count order by location_b) as data
            from (
                select a.location as location_a, b.location as location_b, count(l.*)
                from unnest(%2$L::text[]) a(location)
                cross join unnest(%2$L::text[]) b(location)
                left join locations l on location_a = a.location and location_b = b.location
                group by 1, 2
                ) s
            group by 1
        ) s
        order by 1
        $ex$, list, cities);
end $$;

Use the function and select data from the created view:

select create_locations_view();
select * from locations_view;

 location | Atlanta | London | New York | Sydney | Tokyo 
----------+---------+--------+----------+--------+-------
 Atlanta  | 0       | 1      | 0        | 0      | 0
 London   | 0       | 0      | 0        | 0      | 0
 New York | 0       | 0      | 0        | 0      | 1
 Sydney   | 0       | 1      | 0        | 0      | 0
 Tokyo    | 0       | 0      | 0        | 2      | 0
(5 rows)

I used this method several times but I have no experience with really large data, so I cannot guarantee that it is efficient.