Re: [SQL] getting the values of a complex attribute

Jackson, DeJuan wrote:

> > - create table person (name text);
> > - create table book(name text, author person);
> >
> > - insert into person (name) values ('nicolas');
> > INSERT 691704 1
> > - insert into person (name) values ('julien');
> > INSERT 691705 1
> >
> > - insert into book (name, author) values ('my book', 691704);
> > INSERT 691706 1
> >
> > ok, but cant i write anything but the oid of the person record i want
> > to
> > insert ? (this is my first question)
> Not in that table definition.
>

OK, but what table definition will allow me to write something likeinsert
into book (name, author) values('my book', select * from person where name
= 'nicolas');
or something much more readable than thre oid ??

> try:
> select * from book
> where EXISTS(select person.oid from person
> where person.oid = book.author and
> person.name = 'nicolas');
> or
> select book.* from book, person
> where book.author = person.oid and
> person.name = 'nicolas';
> use the faster one.
>
>

both syntax give me the following error message

ERROR: There is no operator '=' for types 'person' and 'oid'
You will either have to retype this query using an explicit cast,
or you will have to define the operator using CREATE OPERATOR

Another idea ?
When thinking (hardly but ...:-)), I'm rather sure I must use a function or
an operator, perhaps an explicit cast ?
My feeling is that, if it's not possible, there is no interest to be able
to specify a complex type as a row of another table.
Am I right or wrong ?

Any comment ?

--
Nicolas Caillaud
Nicolas(dot)Caillaud(at)mail(dot)dotcom(dot)fr