Re: Self referencing composite datatype

Igor Neyman wrote
>>
>> create type node as (r integer, s integer); alter type node add attribute
>> children node[];
>>
>
> Under 9.2.2 I'm getting an error:
>
> ERROR: composite type node cannot be made a member of itself

I'm not sure why the limitation exists (probably something to do with
avoiding infinite recursion) but even if it could be fixed it wouldn't be
for at least a year (version 9.4 or greater) before you'd see it so you will
need to find an alternative solution to your problem.

So with Chris' suggestion you store the node data in a highly detailed form
with parent node id foreign keys then use a function to dynamically generate
the "children" data. The syntax he is using is intermediate-level
PostgreSQL but well described in the documentation (somewhere). Basically,

table.virtual_column == virtual_column(table)

so by creating a function named "children" taking a "node" record/table-type
as input you can use a shorthand form to actually call the function.

These are equivalent:

SELECT node.*, node.children FROM node
SELECT node.*, children(node) FROM node

noting the fact the "node.*" will NOT give you the children; you must still
explicitly invoke the function somehow.

Other solutions are possible but as we do not know the use case meaningful
but more specific solutions are hard to envision or suggest. Chris'
solution is fairly generic in nature and quite useful once you understand
what exactly is going on.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Self-referencing-composite-datatype-tp5766635p5766662.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.