在源目标表中生成访问矩阵

Question

我在PostgreSQL中有两个表 shasonitance 中的每一行都有2个类ID source_id 和 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)
)

我想在基于继承表中的继承关系中的所有类之间创建访问矩阵。 我尝试此代码：

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

并获得正确的答案，但仅适用于课堂上的9个静态行。

如何使用Dynamic SQL命令在类中获取所有数量的行？

如果我们不能使用SQL进行我们该如何使用PL/PGSQL？

Answer 1

静态解决方案

SQL需要在呼叫时知道每个结果列的名称和类型（因此是其编号）。您无法使用普通SQL动态地从数据中得出结果列。您可以使用数组或文档类型代替单独的列：

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	名称	源
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

动态解决方案

如果您不够好需要对DB服务器进行两个往返的动态SQL：1。生成SQL。 2。执行SQL。使用 crosstab（）功能从其他模块 tablefunc 。 您不熟悉，请首先阅读

1. ：

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;

如果 我们...
的这种形式
2。执行：

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;

...获取：

 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＆lt;＆gt; fiddle 在这里

请参阅：

• 动态替代方案，
• href =“ https://dba.stackexchange.com/a/123006/3684”>动态转换hstore键转换为一组未知键的列

psql的动态执行，但PSQL

仍然对服务器进行了两个圆形交易，但是只有一个命令。
以下两个解决方案都使用psql meta commands，并且仅在psql中起作用！

使用 \ gexec

使用标准交互式终端，您可以将生成的SQL送回Postgres服务器，直接使用 \ 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

相同结果。

使用 \ 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)

请参阅（两者相关的答案）：

• 结果表的定义未知吗？

这些解决方案中有很多微妙之处...

除了

假设有一些机制可以禁止重复并直接与关系相矛盾。喜欢：

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

请参阅：