new idea

From: "sql4-en(dot)narod(dot)ru" <sql4-en(at)narod(dot)ru>
To: pgsql-sql(at)postgresql(dot)org
Subject: new idea
Date: 2007-04-09 06:42:24
Message-ID: 1266433218.20070409094224@narod.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Let me know your opinion about next way of processing and extracting data.
This would very comfortable for delivery xml-data into any program, for example into browser.
Has this idea future ? What are you think ?

1. For exaple, you create SET:

create table a (
id num primary key;
data float;
);
create table b (
id num primary key;
ref num references a(id);
data float;
);
create table c (
id num primary key;
link num references b(id);
data float;
);
insert into a values (1, 12.3);
insert into b values (10, 1, 23.4);
insert into b values (20, 1, 34.5);
insert into b values (30, 1, 45.6);
insert into c values (100,10,56.7);
insert into c values (101,10,67.8);
insert into c values (200,20,78.9);
insert into c values (201,20,89.1);
insert into c values (300,30,91.2);

Request "a.b.c" (of Tree Manipulation Language :) ) will return the following
(term "SET" means, that first table in request is parental table,
next table is branch table)

<a id=1 data=12.3>
<b id=10 data=23.4>
<c id=100 data=56.7/>
<c id=101 data=67.8/>
</b>
<b id=20 data=34.5>
<c id=200 data=78.9/>
<c id=201 data=89.1/>
</b>
<b id=30 data=45.6>
<c id=200 data=91.2/>
</b>
</a>

2. For exaple, you create RELAY-RACE:

create table a (
id num primary key;
ref num references b(id);
data float;
);
create table b (
id num primary key;
link num references c(id);
data float;
);
create table c (
id num primary key;
data float;
);
insert into с values (100, 34.5);
insert into b values (10, 100,23.4);
insert into a values (1, 10, 12.3);

Request "a.b.c" will return the following
(term "RELAY-RACE" means, that first table in request is branch table,
next table is parental table)

<a id=1 data=12.3>
<b id=10 data=23.4>
<c id=100 data=34.5/>
</b>
</a>

---

Let's consider more complicated cases.

1. complicated case for "set"

create table a (
id num primary key;
data float;
);
create table b (
id num primary key;
ref1 num references a(id);
ref2 num references a(id);
data float;
);
create table c (
id num primary key;
lnk1 num references b(id);
lnk2 num references b(id);
data float;
);
insert into a values (1, 12.3);
insert into a values (2, 23.4);
insert into b values (10, 1, 2, 34.5);
insert into b values (20, 1, 2, 45.6);
insert into b values (30, 1, 2, 56.7);
insert into b values (40, 1, 2, 67.8);
insert into c values (100,10,20,78.9);
insert into c values (101,10,20,89.1);
insert into c values (200,30,40,91.2);
insert into c values (201,30,40,88.8);

Request "a.b/ref1.c/lnk1" will return the following

<a id=1 data=12.3>
<b id=10 ref2=2 data=34.5>
<c id=100 lnk2=20 data=78.9/>
<c id=101 lnk2=20 data=89.1/>
</b>
<b id=30 ref2=2 data=56.7>
<c id=200 lnk2=40 data=91.2/>
<c id=201 lnk2=40 data=88.8/>
</b>
</a>

2. complicated case for "relay-race"

create table a (
id num primary key;
ref1 num references b(id);
ref2 num references b(id);
data float;
);
create table b (
id num primary key;
lnk1 num references c(id);
lnk2 num references c(id);
data float;
);
create table c (
id num primary key;
data float;
);
insert into с values (201, 78.9);
insert into с values (200, 67.8);
insert into с values (101, 56.7);
insert into с values (100, 45.6);
insert into b values (20, 200,201,34.5);
insert into b values (10, 100,101,23.4);
insert into a values (1, 10, 20, 12.3);

Request "a/ref1.b/lnk1.c" will return the following

<a id=1 data=12.3>
<b id=10 data=23.4>
<c id=100 data=45.6/>
</b>
</a>

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Andrew Sullivan 2007-04-09 12:21:39 Re: new idea
Previous Message Karthikeyan Sundaram 2007-04-09 01:59:13 Another question in functions