Inheritance multi parent: more than one row returned by a subquery used as an expression

[deem0n]

From the PG docs: A table can inherit from more than one parent table, in which case it has the union of the columns defined by the parent tables.

That means we can have many parents for one child table which make the concept of the PG table inheritance confusing ;-)

If we create table which have many parents, then we will have SQL error with schemainspect: more than one row returned by a subquery used as an expression

schemainspect/schemainspect/pg/sql/relations.sql

Line 43 in 066262d

where child.oid = c.oid)

I would propose returning array of parent tables, but not sure if that is supported by the rest of the schemainspect

Here is the problematic SQL:

schemainspect/schemainspect/pg/sql/relations.sql

Line 37 in 066262d

'"' || nmsp_parent.nspname || '"."' || parent.relname || '"' as parent

You can use

SELECT json_agg(
              '"' || nmsp_parent.nspname || '"."' || parent.relname || '"') as parent

or

SELECT array_agg(
              '"' || nmsp_parent.nspname || '"."' || parent.relname || '"') as parent

and probably even

SELECT array_agg( format('%I.%I', nmsp_parent.nspname, parent.relname)) as parent

But you may want to set quote_all_identifiers to ensure that all names are always quoted with ".

[rtrad89]

I added LIMIT 1 to line 43 to see if this improves the situation, and I am getting reasonable results - all inheritance I have is experimental, so I don't mind.

where child.oid = c.oid LIMIT 1)