pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedgetting 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>
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 21:52 Nicolas Caillaud <Nicolas.Caillaud@mail.dotcom.fr>
parent: Jackson, DeJuan <djackson@cpsgroup.com>
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