Thread: RE: [SQL] getting the values of a complex attribute

> Hi *
>
> I'm new to SQL, and being in the Linux world, I began with Postgresql.
> One day, I wrote the following :
>
> - create table person (name text);
> - create table book(name text, author person);
>
> simple, isn't it ? ;()
sure...

> Now :
>
> - insert into person (name) values ('nicolas');
> INSERT 691704 1
> - insert into person (name) values ('julien');
> INSERT 691705 1
>
> now, to fill book :
> - 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.

> Next :
> insert into book (name,author) values ('other ', 691705);
> INSERT 691707
>
> OK, right now, i want te get all books written by 'nicolas', i want to
> write
>
> select * from book where author.name = 'nicolas';
>
> ERROR:  author: Table does not exist.
>
> select * from book where author = 691706;
>
> ERROR:  There is no operator '=' for types 'person' and 'int4'
>         You will either have to retype this query using an explicit
> cast,
>         or you will have to define the operator using CREATE OPERATOR
>
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.

> Now my second question : what can i do to get (in a simpler manner)
> all the books written by 'nicolas' ?
>
> I think that writing an operator just to do that is a little bit
> complicated :-((
>
> Thank you if someone got the answer .......
>
> --
> Nicolas Caillaud
> Nicolas.Caillaud@mail.dotcom.fr
>
>
>

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.Caillaud@mail.dotcom.fr