Re: Composite datatypes, dynamic member fields

Robert Staudinger writes:
> On Mon, 2002-05-13 at 16:17, Tom Lane wrote:
> > Robert Staudinger <robson(at)stereolyzer(dot)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_v
AS 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/