Re: Composite datatypes, dynamic member fields

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/