sql - postgresql WITH statement add empty records to report -
i'm writing request postgresql database (9.2) describe structure of schema tables. make result of request more readable, want add epmty record after rows, corresponded exact table.
i think easiest way reach that, using statement. experience problems here.
current result rowset part:
"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" "" "" "" "" "" wanted result rowset part:
"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 determine next table description block "addresses" "table" "" "" "" "" "" "" "" "idrec" "integer" "" "" "" "" "" report sql query (for schema namespace 'stm'):
with meta_constr_pk ( select distinct tc.constraint_name, tc.table_name, kcu.column_name, tc.constraint_type information_schema.table_constraints tc join information_schema.key_column_usage kcu on tc.constraint_name::text = kcu.constraint_name::text tc.constraint_type::text = 'primary key' ::text order tc.table_name ), meta_constr_fk ( select distinct tc.table_name, kcu.column_name, ccu.table_name foreign_table_name, ccu.column_name foreign_column_name, tc.constraint_type 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 tc.constraint_type::text = 'foreign key' ::text order tc.table_name ), main_select ( select case when a.attname = 'tableoid' c.relname else '' end table_name, replace(a.attname, 'tableoid', 'table') column_name, replace(format_type(a.atttypid, a.atttypmod), 'oid', '') format_type, coalesce( coalesce( col_description(c.oid, a.attnum), obj_description(c.oid)), '' ) description, case when pk.constraint_type not null 'pk' else '' end is_pk, case when col.is_nullable = 'yes' 'y' else '' end is_nullable, coalesce( fk.foreign_table_name, '') foreign_table, coalesce(fk.foreign_column_name, '') foreign_column pg_class c join pg_attribute 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 , col.column_name = a.attname ) left join meta_constr_pk pk on (col.table_name = pk.table_name , col.column_name = pk.column_name ) left join meta_constr_fk fk on (col.table_name = fk.table_name , col.column_name = fk.column_name ) n.nspname = 'stm' , c.relkind = 'r'::"char" , ( a.attnum >= 0 or a.attnum = -7 ) -- "-7" tableoid order c.relname, coalesce( col.ordinal_position, 0 ) ) select * main_select m; also query simplification tips accepted gratitude!
you cross join query ad-hoc table this:
cross join (values(0),(1)) l(line) and make l.line=1 connects row connecting table name , name doesn't displayed if l.line=1 :
with meta_constr_pk ( select distinct tc.constraint_name, tc.table_name, kcu.column_name, tc.constraint_type information_schema.table_constraints tc join information_schema.key_column_usage kcu on tc.constraint_name::text = kcu.constraint_name::text tc.constraint_type::text = 'primary key' ::text order tc.table_name ), meta_constr_fk ( select distinct tc.table_name, kcu.column_name, ccu.table_name foreign_table_name, ccu.column_name foreign_column_name, tc.constraint_type 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 tc.constraint_type::text = 'foreign key' ::text order tc.table_name ), main_select ( select case when a.attname = 'tableoid' , l.line=0 c.relname else '' end table_name, case when l.line=0 replace(a.attname, 'tableoid', 'table') else '' end column_name, replace(format_type(a.atttypid, a.atttypmod), 'oid', '') format_type, coalesce( coalesce( col_description(c.oid, a.attnum), obj_description(c.oid)), '' ) description, case when pk.constraint_type not null 'pk' else '' end is_pk, case when col.is_nullable = 'yes' 'y' else '' end is_nullable, coalesce( fk.foreign_table_name, '') foreign_table, coalesce(fk.foreign_column_name, '') foreign_column pg_class c cross join (values(0),(1)) l(line) join pg_attribute on (a.attrelid = c.oid) , (l.line=0 or (l.line=1 , 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 , col.column_name = a.attname ) , l.line=0 left join meta_constr_pk pk on (col.table_name = pk.table_name , col.column_name = pk.column_name ) , l.line=0 left join meta_constr_fk fk on (col.table_name = fk.table_name , col.column_name = fk.column_name ) , l.line=0 c.relkind = 'r'::"char" , n.nspname = 'public' , ( a.attnum >= 0 or a.attnum = -7) -- "-7" tableoid order c.relname, l.line, coalesce( col.ordinal_position, 0 ) ) select * main_select m; fiddle (it displays l.line easier understanding)
the problem approach 1 empty row @ end of table, can averted use of window function row_number().
Comments
Post a Comment