Re: Self referencing composite datatype

[Chris Travers <chris.travers@xxxxxxxxx>]

On Wed, Aug 7, 2013 at 4:57 AM, Sameer Thakur <samthakur74@xxxxxxxxx> wrote:

Hello,

I wanted to create a composite datatype to represent a Node. So it would have a few attributes and an array of type Node which is the children of this node.

create type Node as (r integer, s integer, children Node []); 

But i get error type Node[] does not exist. I understand that Node is not defined hence the error.

But how do i get around this problem? 

What exactly are you trying to accomplish?  I can think of a number of ways.

For example, suppose we have a table like:

create table node (

   id int primary key,

   parent int references node(id),

   content text not null

);

We could create a function like this:

CREATE FUNCTION children(node) RETURNS node[] LANGUAGE SQL AS

$$

SELECT array_agg(node) FROM node WHERE parent=$1.id;

$$;

Then we could still do:

select n.children FROM node n WHERE id = 123;

Note that causes two separate scans, but should work.

Best Wishes,

Chris Travers 

regards

Sameer

--

Best Wishes,

Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor lock-in.

http://www.efficito.com/learn_more.shtml