Databases
StackOverflow2013
Postsdbo
POpostgresql WITH statement add empty records to report
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POpostgresql WITH statement add empty records to report
    primarykey
    data
    text
    <p>I'm writing a request for postgresql database (9.2) to describe the structure of all schema tables. To make the result of the request more readable, I want to add an epmty record after rows, corresponded to the exact table.</p> <p>I think the easiest way to reach that, is using WITH statement. So I experience some problems here.</p> <p>Current result rowset part:</p> <pre><code>"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" "" "" "" "" "" </code></pre> <p>Wanted result rowset part:</p> <pre><code>"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" "" "" "" "" "" "" "" "" "" "" -- &lt;-- empty record to determine next table description block "addresses" "TABLE" "" "" "" "" "" "" "" "IDRec" "integer" "" "" "" "" "" </code></pre> <p>Report SQL query (for my schema namespace 'stm'):</p> <pre><code>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 &gt;= 0 or a.attnum = -7 ) -- "-7" for tableoid order by c.relname, COALESCE( col.ordinal_position, 0 ) ) select * from main_select m; </code></pre> <p>Also any query simplification tips would be accepted with gratitude!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USClodoaldo Neto
    1. USxacinay
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload