Обсуждение: Self referencing composite datatype
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?
regards
Sameer
On Wed, Aug 7, 2013 at 4:57 AM, Sameer Thakur <samthakur74@gmail.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
regardsSameer
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.
Sameer Thakur 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? In theory if you are using 9.1 or later you can first create the node type and then alter it to include a children attribute with the self-referencing type-array. begin; create type node as (r integer, s integer); alter type node add attribute children node[]; end; I'm running 9.0 so cannot readily test this at the moment. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Self-referencing-composite-datatype-tp5766635p5766651.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of David Johnston > Sent: Wednesday, August 07, 2013 10:35 AM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Self referencing composite datatype > > Sameer Thakur 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? > > In theory if you are using 9.1 or later you can first create the node type and > then alter it to include a children attribute with the self-referencing type- > array. > > begin; > > create type node as (r integer, s integer); alter type node add attribute > children node[]; > > end; > > I'm running 9.0 so cannot readily test this at the moment. > > David J. > Under 9.2.2 I'm getting an error: ERROR: composite type node cannot be made a member of itself ********** Error ********** ERROR: composite type node cannot be made a member of itself SQL state: 42P16 Regards, Igor Neyman
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.
On Wed, Aug 7, 2013 at 4:57 AM, Sameer Thakur <samthakur74@gmail.com> wrote: > 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? I just wonder how are you going to use this kind of types? In 9.3 you will be able to use foreign keys with arrays like it is describe here http://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/ eg. create table node as ( id integer primary key, r integer, s integer, children integer[] element references node ); so you could download 9.3rc2 and experimant with it. Now (on <=9.2.x) you can create the table without FK create table node as ( id integer primary key, r integer, s integer, children integer[] ); and check integrity by triggers. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray.ru@gmail.com
On Wed, Aug 7, 2013 at 7:11 PM, Sergey Konoplev <gray.ru@gmail.com> wrote: > so you could download 9.3rc2 and experimant with it. Sorry, 9.3beta2 of course. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray.ru@gmail.com
On Wed, Aug 7, 2013 at 4:57 AM, Sameer Thakur <samthakur74@gmail.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.
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.
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
On Aug 8, 2013, at 4:11, Sergey Konoplev <gray.ru@gmail.com> wrote: > create table node as ( > id integer primary key, > r integer, s integer, > children integer[] element references node > ); > > so you could download 9.3rc2 and experimant with it. > > Now (on <=9.2.x) you can create the table without FK > > create table node as ( > id integer primary key, > r integer, s integer, > children integer[] > ); > > and check integrity by triggers. Or, instead of attempting to reference all child nodes from the parent, reference the parent node from each child node. That's been supported in PG versions like forever and can be queried fairly efficiently using recursive CTE's since PG 9. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
On Wed, Aug 7, 2013 at 11:38 PM, Alban Hertroys <haramrae@gmail.com> wrote: > On Aug 8, 2013, at 4:11, Sergey Konoplev <gray.ru@gmail.com> wrote: >> create table node as ( >> id integer primary key, >> r integer, s integer, >> children integer[] >> ); >> >> and check integrity by triggers. > > > Or, instead of attempting to reference all child nodes from the parent, reference the parent node from each child node. > That's been supported in PG versions like forever and can be queried fairly efficiently using recursive CTE's since PG9. That particular moment I thought it was about graphs. Later OP mentioned tree, so yes, it is better to use parent reference here. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray.ru@gmail.com
On Wed, Aug 7, 2013 at 11:38 PM, Alban Hertroys <haramrae@gmail.com> wrote:
On Aug 8, 2013, at 4:11, Sergey Konoplev <gray.ru@gmail.com> wrote:Or, instead of attempting to reference all child nodes from the parent, reference the parent node from each child node.
> create table node as (
> id integer primary key,
> r integer, s integer,
> children integer[] element references node
> );
>
> so you could download 9.3rc2 and experimant with it.
>
> Now (on <=9.2.x) you can create the table without FK
>
> create table node as (
> id integer primary key,
> r integer, s integer,
> children integer[]
> );
>
> and check integrity by triggers.
That's been supported in PG versions like forever and can be queried fairly efficiently using recursive CTE's since PG 9.
If you do this, have a position number, and use that for ordering. You need some sort of ordinality here.
Best Wishes,
Chris Travers
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.