Search Postgresql Archives

Re: Self referencing composite datatype

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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.


Thank you. 
I am trying to capture plan statistics for every node in the plan tree. For this i have plan view which has plan specific information like planid,plan_text, and root_node of type Node.

Type Node has node specific statistics like estimated_startup_cost, estimated_actual_cost etc. It also has child_nodes of type Node[]. i face the problem of self referencing composite type in defining Node type. 

The reason i wanted a type Node is because while initializing memory for my contrib module i have GUC parameter specifying max number of Nodes. I was thinking that it is possible to initialize memory with a sizeof(Node). 

Still trying to figure out how using a table storing Node will help me in figuring out how much initial memory can be allocated

regards
Sameer

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux