Thread: Composite datatypes, dynamic member fields
Hello,
I'm planning to build some kind of simple object oriented database and
came across PostgreSQL because of it's nearly unlimited ways of
extensibility.
The documentation is really good an I learned a lot about composite
datatypes, how to program them in c, create indices ... from the "Server
Programming" section of the "Developer Manual".
The problem in my case is that I don't know the names of the attributes
beforehand so I can't do a table bases mapping with one table for each
type of object ("class"). So I thought It would maybe be possible to
store the objects always in the same composed datatype - with "dynamic
member fields".
Ok this may be completely insane but ...
Maybe you could just tell me if it's worth to dig deeper.
Robert Staudinger
Robert Staudinger writes:
> I'm planning to build some kind of simple object oriented database and
> came across PostgreSQL because of it's nearly unlimited ways of
> extensibility.
> The documentation is really good an I learned a lot about composite
> datatypes, how to program them in c, create indices ... from the "Server
> Programming" section of the "Developer Manual".
> The problem in my case is that I don't know the names of the attributes
> beforehand so I can't do a table bases mapping with one table for each
> type of object ("class"). So I thought It would maybe be possible to
> store the objects always in the same composed datatype - with "dynamic
> member fields".
I use inheritance in such a case.
(see INHERITS in CREATE TABLE)
Table with common columns as a base class
and different tables for classes with
inherited base class.
Multiple and multilevel inheritanse possible.
> Ok this may be completely insane but ...
>
> Maybe you could just tell me if it's worth to dig deeper.
>
> Robert Staudinger
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
--
@BABOLO http://links.ru/
> I use inheritance in such a case. > (see INHERITS in CREATE TABLE) > Table with common columns as a base class > and different tables for classes with > inherited base class. > Multiple and multilevel inheritanse possible. The problem in my case is that even a group of objects which belong together might not have even one common attribute. One idea is to implement a . operator on a basic data type and return the value for the corresponding field from the "operator function". E.g. "select * from mytable where mytype.mymember='x'" could call something like mytype_member_read( mytype, member_name ) but I'm not sure which datatype member_name would be in this case. Unfortunately I don't have any idea how to handle updates on such fields with a user defined member access operator yet. Does anybody have any advice on that? Robert Staudinger PS: Please excuse if this explanation doesn't make perfect sense to you - it's a little hard for me to say in English what I mean.
> I use inheritance in such a case. > (see INHERITS in CREATE TABLE) > Table with common columns as a base class > and different tables for classes with > inherited base class. > Multiple and multilevel inheritanse possible. The problem in my case is that even a group of objects which belong together might not have even one common attributes. One idea is to implement a . operator on a basic data type and return the value for the corresponding field from the "operator function". E.g. "select * from TABLE where mytype.mymember='x'" could call something like mytype_member_access( mytype, member_name ) Unfortunately I don't have any idea how to handle updates on fields yet. Does anybody have any advice on that? Robert Staudinger PS: Please excuse if this explanation doesn't make much sense to you - it's a little hard for me to say in English what I mean.
Robert Staudinger <robson@stereolyzer.net> writes:
> One idea is to implement a . operator on a basic data type and return
> the value
> for the corresponding field from the "operator function".
> E.g.
> "select * from mytable where mytype.mymember='x'"
> could call something like
> mytype_member_read( mytype, member_name )
> but I'm not sure which datatype member_name would be in this case.
PG has always had the ability to define functions that could be
notationally treated as fields. A trivial example:
test72=# create table tours(depart date, return date);
CREATE
test72=# insert into tours values('2002-01-01', '2002-01-10');
INSERT 525275 1
test72=# insert into tours values('2001-12-15', '2002-01-05');
INSERT 525276 1
test72=# create function numdays(tours) returns int as '
test72'# select $1.return - $1.depart' language sql;
CREATE
test72=# select *, tours.numdays from tours; depart | return | numdays
------------+------------+---------2002-01-01 | 2002-01-10 | 92001-12-15 | 2002-01-05 | 21
(2 rows)
The computed field doesn't quite have the same status as real fields
--- notice that * doesn't know about it in the above example --- but
it's a useful technique anyway.
regards, tom lane
On Mon, 2002-05-13 at 16:17, Tom Lane wrote:
> Robert Staudinger <robson@stereolyzer.net> writes:
> > One idea is to implement a . operator on a basic data type and return
> > the value
> > for the corresponding field from the "operator function".
> > E.g.
> > "select * from mytable where mytype.mymember='x'"
> > could call something like
> > mytype_member_read( mytype, member_name )
> > but I'm not sure which datatype member_name would be in this case.
>
> PG has always had the ability to define functions that could be
> notationally treated as fields. A trivial example:
>
> test72=# create table tours(depart date, return date);
> CREATE
> test72=# insert into tours values('2002-01-01', '2002-01-10');
> INSERT 525275 1
> test72=# insert into tours values('2001-12-15', '2002-01-05');
> INSERT 525276 1
> test72=# create function numdays(tours) returns int as '
> test72'# select $1.return - $1.depart' language sql;
> CREATE
> test72=# select *, tours.numdays from tours;
> depart | return | numdays
> ------------+------------+---------
> 2002-01-01 | 2002-01-10 | 9
> 2001-12-15 | 2002-01-05 | 21
> (2 rows)
>
> The computed field doesn't quite have the same status as real fields
> --- notice that * doesn't know about it in the above example --- but
> it's a useful technique anyway.
>
> regards, tom lane
Hmm I don't know if this solves my problem.
E.g.
I want to store a group of linked objects. Maybe x(ht)ml could be an
example:
<table bgcolor="#000000">
<tr>
<td border="1">one</td>
<td cellpadding="2">two</td>
<td border="1">three</td>
</tr>
</table>
these should be stored in a table
( column "object" is of a base type written in c and should hold
arbitrary string attributes)
id parentid object
------------------
1 0 table
2 1 tr
3 2 td1
4 2 td2
5 2 td3
to be able to query something like
"select * from mytable where object.border='1'"
i'd probably need a . operator on "object" if "object" is a base type
implemented in c which holds the attributes internally - just as
described in my previous post.
The problem is that i don't know how to handle
"update mytable set object.margin='2' where parentid='2'"
Please note that I'm just thinking about how OR mapping could be done by
taking advantage of postgresql's extensibility - this is not a real
world problem ;-)
Thanks for sharing
Robert Staudinger
On Mon, May 13, 2002 at 10:17:55AM -0400, Tom Lane wrote: > > The computed field doesn't quite have the same status as real fields > --- notice that * doesn't know about it in the above example --- but > it's a useful technique anyway. That particular shortcoming can be overcome by wrapping the computed fields in a view: create view tour_lengths AS select *, tours.numdays from tours; test=# create view tours_plus as select *, tours.numdays from tours; CREATE test=# select * from tours_plus; depart | return | numdays ------------+------------+---------2002-01-01 | 2002-01-10 | 92001-12-15 | 2002-01-05 | 21 (2 rows) Ross
Robert Staudinger writes:
> On Mon, 2002-05-13 at 16:17, Tom Lane wrote:
> > Robert Staudinger <robson@stereolyzer.net> writes:
> > > One idea is to implement a . operator on a basic data type and return
> > > the value
> > > for the corresponding field from the "operator function".
> > > E.g.
> > > "select * from mytable where mytype.mymember='x'"
> > > could call something like
> > > mytype_member_read( mytype, member_name )
> > > but I'm not sure which datatype member_name would be in this case.
> >
> > PG has always had the ability to define functions that could be
> > notationally treated as fields. A trivial example:
> >
> > test72=# create table tours(depart date, return date);
> > CREATE
> > test72=# insert into tours values('2002-01-01', '2002-01-10');
> > INSERT 525275 1
> > test72=# insert into tours values('2001-12-15', '2002-01-05');
> > INSERT 525276 1
> > test72=# create function numdays(tours) returns int as '
> > test72'# select $1.return - $1.depart' language sql;
> > CREATE
> > test72=# select *, tours.numdays from tours;
> > depart | return | numdays
> > ------------+------------+---------
> > 2002-01-01 | 2002-01-10 | 9
> > 2001-12-15 | 2002-01-05 | 21
> > (2 rows)
> >
> > The computed field doesn't quite have the same status as real fields
> > --- notice that * doesn't know about it in the above example --- but
> > it's a useful technique anyway.
> >
> > regards, tom lane
>
> Hmm I don't know if this solves my problem.
> E.g.
> I want to store a group of linked objects. Maybe x(ht)ml could be an
> example:
>
> <table bgcolor="#000000">
> <tr>
> <td border="1">one</td>
> <td cellpadding="2">two</td>
> <td border="1">three</td>
> </tr>
> </table>
>
> these should be stored in a table
> ( column "object" is of a base type written in c and should hold
> arbitrary string attributes)
>
> id parentid object
> ------------------
> 1 0 table
> 2 1 tr
> 3 2 td1
> 4 2 td2
> 5 2 td3
>
> to be able to query something like
> "select * from mytable where object.border='1'"
> i'd probably need a . operator on "object" if "object" is a base type
> implemented in c which holds the attributes internally - just as
> described in my previous post.
>
> The problem is that i don't know how to handle
> "update mytable set object.margin='2' where parentid='2'"
>
> Please note that I'm just thinking about how OR mapping could be done by
> taking advantage of postgresql's extensibility - this is not a real
> world problem ;-)
OK
If list of attributes must not be closed,
then in your example use table of attributes
CREATE TABLE attributes ( id int? oid? , attname name? text? , value text? , PRIMARY KEY(id,attname)
)
;
CREATE VIEW attributes_vAS SELECT * FROM attributes
;
CREATE RULE attributes_r ON INSERT TO attributes_v DO INSTEAD ( DELETE FROM attributes WHERE (attributes.id,
attributes.attname)= (new.id, new.attname) ; INSERT INTO attributes VALUES(new.id, new.attname, new.value)
)
;
so your examples will
SELECT mytable.* FROM mytable NATURAL JOIN attributes WHERE border='1';
and
INSERT INTO attributes_v SELECT id, 'margin', '2' FROM mytable WHERE parentid='2';
VIEW attributes_v used because I was in break twice in 7.1.3 -> 7.2 and
7.2 -> 7.2.1 transition and do not want remember of
order of apply of rules in different versions,
so I do it explicit.
May be it can be written shorter,
but danderous (IMHO).
As usual a lot of conviniense and optimisation
can be made dependant of task.
My examples are cutted from my big work and oversimplifyed,
so usual causes.
Sorry for bad English.
--
@BABOLO http://links.ru/