pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
getting the values of a complex attribute
3+ messages / 2 participants
[nested] [flat]

* getting the values of a complex attribute
@ 1998-06-07 22:05 Nicolas Caillaud <Nicolas.Caillaud@mail.dotcom.fr>
  0 siblings, 0 replies; 3+ messages in thread

From: Nicolas Caillaud @ 1998-06-07 22:05 UTC (permalink / raw)
  To: pgsql-sql

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 ? ;()

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)

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

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






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* RE: [SQL] getting the values of a complex attribute
@ 1998-06-08 18:12 Jackson, DeJuan <djackson@cpsgroup.com>
  1998-06-08 21:52 ` Re: [SQL] getting the values of a complex attribute Nicolas Caillaud <Nicolas.Caillaud@mail.dotcom.fr>
  0 siblings, 1 reply; 3+ messages in thread

From: Jackson, DeJuan @ 1998-06-08 18:12 UTC (permalink / raw)
  To: Nicolas.Caillaud@mail.dotcom.fr; pgsql-sql

> 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
> 
> 
> 



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] getting the values of a complex attribute
  1998-06-08 18:12 RE: [SQL] getting the values of a complex attribute Jackson, DeJuan <djackson@cpsgroup.com>
@ 1998-06-08 21:52 ` Nicolas Caillaud <Nicolas.Caillaud@mail.dotcom.fr>
  0 siblings, 0 replies; 3+ messages in thread

From: Nicolas Caillaud @ 1998-06-08 21:52 UTC (permalink / raw)
  To: Jackson, DeJuan <djackson@cpsgroup.com>; pgsql-sql

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






^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~1998-06-08 21:52 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-06-07 22:05 getting the values of a complex attribute Nicolas Caillaud <Nicolas.Caillaud@mail.dotcom.fr>
1998-06-08 18:12 RE: [SQL] getting the values of a complex attribute Jackson, DeJuan <djackson@cpsgroup.com>
1998-06-08 21:52 ` Nicolas Caillaud <Nicolas.Caillaud@mail.dotcom.fr>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox