postgresql WITH语句添加空记录到报表

Question

我正在为postgresql数据库(9.2)写一个请求，描述所有模式表的结构。为了使请求的结果更具可读性，我想在行之后添加一个空记录，对应于确切的表。

我认为最简单的方法是使用WITH语句。所以我在这里遇到了一些问题。

当前结果行集部分:

"table_name"    "column_name"   "format_type"   "description"   "is_pk" "is_nullable"   "foreign_table" "foreign_column"
"active_keys"   "TABLE" ""  "Login activation keys" ""  ""  ""  ""
""  "Key"   "character varying(900)"    "Activation key"    "PK"    ""  ""  ""
""  "LoginID"   "bigint"    "Activated login   ""   "Y" ""  ""
"addresses" "TABLE" ""  ""  ""  ""  ""  ""
""  "IDRec" "integer"   ""  ""  ""  ""  ""

需要的结果行集部分:

"table_name"    "column_name"   "format_type"   "description"   "is_pk" "is_nullable"   "foreign_table" "foreign_column"
"active_keys"   "TABLE" ""  "Login activation keys" ""  ""  ""  ""
""  "Key"   "character varying(900)"    "Activation key"    "PK"    ""  ""  ""
""  "LoginID"   "bigint"    "Activated login   ""   "Y" ""  ""
""  ""  ""  ""  ""  ""  ""  "" -- <-- empty record to determine next table description block
"addresses" "TABLE" ""  ""  ""  ""  ""  ""
""  "IDRec" "integer"   ""  ""  ""  ""  ""

报告SQL查询(对于我的模式命名空间'stm'):

with  meta_constr_pk as (
    SELECT DISTINCT tc.constraint_name,
        tc.table_name,
        kcu.column_name,
        tc.constraint_type
    FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage kcu ON tc.constraint_name::text = kcu.constraint_name::text
    WHERE tc.constraint_type::text = 'PRIMARY KEY' ::text
    ORDER BY tc.table_name ),
meta_constr_fk as (
    SELECT DISTINCT tc.table_name,
        kcu.column_name,
        ccu.table_name AS foreign_table_name,
        ccu.column_name AS foreign_column_name,
        tc.constraint_type
    FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage kcu ON tc.constraint_name::text = kcu.constraint_name::text
    JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name::text = tc.constraint_name::text
    WHERE tc.constraint_type::text = 'FOREIGN KEY' ::text
    ORDER BY tc.table_name ),
main_select AS (
    select    case when a.attname = 'tableoid' then c.relname else '' end as table_name,
        replace(a.attname, 'tableoid', 'TABLE') as column_name,
        replace(format_type(a.atttypid, a.atttypmod), 'oid', '') as format_type,   
        coalesce( coalesce( col_description(c.oid, a.attnum),
        obj_description(c.oid)), '' ) as description,
        case when pk.constraint_type is not null then 'PK' else '' end  is_pk,
        case when col.is_nullable = 'YES' then 'Y' else '' end is_nullable,  
        coalesce( fk.foreign_table_name, '') foreign_table,
        coalesce(fk.foreign_column_name, '') foreign_column
    from pg_class c
    join pg_attribute a on (a.attrelid = c.oid)
    join pg_namespace n on (n.oid = c.relnamespace)
    left join information_schema.columns col on (col.table_name = c.relname and col.column_name = a.attname )   
    left join meta_constr_pk pk on (col.table_name = pk.table_name and col.column_name = pk.column_name )
    left join meta_constr_fk fk on (col.table_name = fk.table_name and col.column_name = fk.column_name )
    where
        n.nspname = 'stm' and c.relkind = 'r'::"char" 
        and ( a.attnum >= 0 or a.attnum = -7 ) -- "-7" for tableoid 
    order by c.relname, COALESCE( col.ordinal_position, 0 )
)
select *
from main_select m;

也任何查询简化提示将被接受与感激!

Answer 1

您可以像这样将查询与临时表交叉连接:

CROSS JOIN (VALUES(0),(1)) AS l(line)

然后使l.line=1只与连接表名的行连接如果l.line=1:

则不显示该名称

WITH  meta_constr_pk AS (
    SELECT DISTINCT tc.constraint_name,
        tc.table_name,
        kcu.column_name,
        tc.constraint_type
    FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage kcu ON tc.constraint_name::text = kcu.constraint_name::text
    WHERE tc.constraint_type::text = 'PRIMARY KEY' ::text
    ORDER BY tc.table_name ),
meta_constr_fk AS (
    SELECT DISTINCT tc.table_name,
        kcu.column_name,
        ccu.table_name AS foreign_table_name,
        ccu.column_name AS foreign_column_name,
        tc.constraint_type
    FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage kcu ON tc.constraint_name::text = kcu.constraint_name::text
    JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name::text = tc.constraint_name::text
    WHERE tc.constraint_type::text = 'FOREIGN KEY' ::text
    ORDER BY tc.table_name ),
main_select AS (
    SELECT
  CASE WHEN a.attname = 'tableoid' AND l.line=0 THEN c.relname ELSE '' END AS TABLE_NAME,
        CASE WHEN l.line=0 THEN replace(a.attname, 'tableoid', 'TABLE') ELSE '' END AS COLUMN_NAME,
        replace(format_type(a.atttypid, a.atttypmod), 'oid', '') AS format_type,
        coalesce( coalesce( col_description(c.oid, a.attnum),
        obj_description(c.oid)), '' ) AS description,
        CASE WHEN pk.constraint_type IS NOT NULL THEN 'PK' ELSE '' END  is_pk,
        CASE WHEN col.is_nullable = 'YES' THEN 'Y' ELSE '' END is_nullable,
        coalesce( fk.foreign_table_name, '') foreign_table,
        coalesce(fk.foreign_column_name, '') foreign_column
    FROM pg_class c
    CROSS JOIN (VALUES(0),(1)) AS l(line)
    JOIN pg_attribute a ON (a.attrelid = c.oid) AND (l.line=0 OR (l.line=1 AND a.attname = 'tableoid'))
    JOIN pg_namespace n ON (n.oid = c.relnamespace) --AND l.line=0
    LEFT JOIN information_schema.columns col ON (col.TABLE_NAME = c.relname AND col.COLUMN_NAME = a.attname ) AND l.line=0
    LEFT JOIN meta_constr_pk pk ON (col.TABLE_NAME = pk.TABLE_NAME AND col.COLUMN_NAME = pk.COLUMN_NAME ) AND l.line=0
    LEFT JOIN meta_constr_fk fk ON (col.TABLE_NAME = fk.TABLE_NAME AND col.COLUMN_NAME = fk.COLUMN_NAME ) AND l.line=0
    WHERE
      c.relkind = 'r'::"char"
      AND
      n.nspname = 'public'
      AND
      ( a.attnum >= 0 OR a.attnum = -7) -- "-7" for tableoid
    ORDER BY c.relname, l.line, COALESCE( col.ordinal_position, 0 )
)
SELECT *
FROM main_select m;

fiddle(为了更容易理解，它显示l.行)

这种方法的唯一问题是您在表的末尾也会得到一个空行，这可以通过使用窗口函数row_number()来避免。

Answer 2

为了使请求的结果更具可读性，我想在行之后添加一个空记录，对应于确切的表。

这是一个表现层面的问题。它与输出看起来如何有关，而与输出是什么无关。

这意味着它是更好地处理您的报告工具，而不是SQL。SQL提供数据;报表工具提供布局