Re: Self referencing composite datatype

On Wed, Aug 7, 2013 at 4:57 AM, Sameer Thakur <samthakur74(at)gmail(dot)com> 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