sql postgresql plpgsql dynamic-sql crosstab

Generate matrix of access in source-target table

I have two tables in PostgreSQL, class and inheritance.
Each row in inheritance has 2 class IDs source_id and target_id:

CREATE TABLE public.class (
    id bigint NOT NULL DEFAULT nextval('class_id_seq'::regclass),
    name character varying(500) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT class_pkey PRIMARY KEY (id)
)

CREATE TABLE public.inheritance (
    id bigint NOT NULL DEFAULT nextval('inherited_id_seq'::regclass),
    source_id bigint NOT NULL,
    target_id bigint NOT NULL,
    CONSTRAINT inherited_pkey PRIMARY KEY (id),
    CONSTRAINT inherited_source_id_fkey FOREIGN KEY (source_id)
        REFERENCES public.class (id),
    CONSTRAINT inherited_target_id_fkey FOREIGN KEY (target_id)
        REFERENCES public.class (id)
)

I want to create Access Matrix between all classes based in inheritance relationship in inheritance table. I try this code:

select * , case when id in (select target_id from inheritance where source_id=1) then 1 else 0 end as "1"   
         , case when id in (select target_id from inheritance where source_id=2) then 1 else 0 end as "2"
         , case when id in (select target_id from inheritance where source_id=3) then 1 else 0 end as "3"
         , case when id in (select target_id from inheritance where source_id=4) then 1 else 0 end as "4"
         , case when id in (select target_id from inheritance where source_id=5) then 1 else 0 end as "5"
         , case when id in (select target_id from inheritance where source_id=6) then 1 else 0 end as "6"
         , case when id in (select target_id from inheritance where source_id=7) then 1 else 0 end as "7"
         , case when id in (select target_id from inheritance where source_id=8) then 1 else 0 end as "8"
         , case when id in (select target_id from inheritance where source_id=9) then 1 else 0 end as "9"
from class

and get the right answer, but it's just for 9 static rows in class.

How can I get all number of rows in class using a dynamic SQL command?

If we can't do it with SQL, how can we do it with PL/pgSQL?

Solution

Static solution

SQL demands to know name and type of each result column (and consequently their number) at call time. You cannot derive result columns from data dynamically with plain SQL. You can use an array or a document type instead of separate columns:

SELECT *
FROM   class c
LEFT   JOIN (
   SELECT target_id AS id, array_agg(source_id) AS sources
   FROM  (SELECT source_id, target_id FROM inheritance i ORDER BY 1,2) sub
   GROUP  BY 1
   ) i USING (id);

id	name	sources
1	c1	{2,3,4}
2	c2	{5}
3	c3	{5,6,7}
4	c4	{7}
5	c5	{8}
6	c6	{9}
7	c7	{9}
8	c8	null
9	c9	null

Dynamic solution

If that's not good enough you need dynamic SQL with two round-trips to the DB server: 1. Generate SQL. 2. Execute SQL. Using the crosstab() function from the additional module tablefunc. If you are unfamiliar, read this first:

PostgreSQL Crosstab Query

Generate SQL:

SELECT format(
$q$SELECT *
FROM   class c
LEFT   JOIN crosstab(
   'SELECT target_id, source_id, 1 FROM inheritance ORDER BY 1,2'
 , 'VALUES (%s)'
   ) AS ct (id int, %s int)
     USING (id)
ORDER  BY id;
$q$
        , string_agg(c.id::text, '), (')
        , string_agg('"' || c.id || '"', ' int, ')
      )
FROM  (SELECT id FROM class ORDER BY 1) c;

Returns a query of this form, which we ...
2. Execute:

SELECT *
FROM   class c
LEFT   JOIN crosstab(
   'SELECT target_id, source_id, 1 FROM inheritance ORDER BY 1,2'
 , 'VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9)'
   ) AS ct (id int, "1" int, "2" int, "3" int, "4" int, "5" int, "6" int, "7" int, "8" int, "9" int)
     USING (id)
ORDER  BY id;

... to get:

 id | name | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9
----+------+---+---+---+---+---+---+---+---+---
  1 | c1   |   | 1 | 1 | 1 |   |   |   |   |  
  2 | c2   |   |   |   |   | 1 |   |   |   |  
  3 | c3   |   |   |   |   | 1 | 1 | 1 |   |  
  4 | c4   |   |   |   |   |   |   | 1 |   |  
  5 | c5   |   |   |   |   |   |   |   | 1 |  
  6 | c6   |   |   |   |   |   |   |   |   | 1
  7 | c7   |   |   |   |   |   |   |   |   | 1
  8 | c8   |   |   |   |   |   |   |   |   |  
  9 | c9   |   |   |   |   |   |   |   |   |

db<>fiddle here

See:

Dynamic execution with psql

Still two round-trips to the server, but only a single command.
Both of the following solutions use psql meta-commands and only work from within psql!

With `\gexec`

Using the standard interactive terminal, you can feed the generated SQL back to the Postgres server for execution directly with \gexec:

test=> SELECT format(
$q$SELECT *
FROM   class c
LEFT   JOIN crosstab(
'SELECT target_id, source_id, 1 FROM inheritance ORDER BY 1,2'
, 'VALUES (%s)'
) AS ct (id int, %s int)
  USING (id)
ORDER  BY id;
$q$
     , string_agg(c.id::text, '), (')
     , string_agg('c' || c.id, ' int, ')
   )
FROM  (SELECT id FROM class ORDER BY 1) c\gexec

Same result.

With `\crosstabview`

test=> SELECT *
test-> FROM   class c
test-> LEFT   JOIN (
test(>    SELECT target_id AS id, source_id, 1 AS val
test(>    FROM   inheritance
test(>    ) i USING (id)
test-> \crosstabview id source_id val
 id | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |  
----+---+---+---+---+---+---+---+---+--
  1 | 1 | 1 | 1 |   |   |   |   |   | 
  2 |   |   |   | 1 |   |   |   |   | 
  3 |   |   |   | 1 | 1 | 1 |   |   | 
  4 |   |   |   |   |   | 1 |   |   | 
  5 |   |   |   |   |   |   | 1 |   | 
  6 |   |   |   |   |   |   |   | 1 | 
  7 |   |   |   |   |   |   |   | 1 | 
  8 |   |   |   |   |   |   |   |   | 
  9 |   |   |   |   |   |   |   |   | 
(9 rows)

See (with related answers for both):

How do I generate a pivoted CROSS JOIN where the resulting table definition is unknown?

There are lots of subtleties in these solutions ...

Aside

Assuming there are some mechanisms in place to disallow duplicates and directly contradicting relationships. Like:

CREATE UNIQUE INDEX inheritance_uni_idx
ON inheritance (GREATEST(source_id, target_id), LEAST(source_id, target_id));