pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Nicolas Caillaud <Nicolas.Caillaud@mail.dotcom.fr>
To: pgsql-sql@postgreSQL.org
Subject: Table with attributes of complex type (again)
Date: Mon, 15 Jun 1998 00:42:18 +0200
Message-ID: <358451C9.FFB49170@mail.dotcom.fr> (raw)

Here is a little SQL story I wrote for myself :-)

create table person (name text );
CREATE

create table book (title text, author person);
CREATE

insert into person values ('nicolas');
INSERT 692344 1

insert into person values ('julien');
INSERT 692345 1

create function person_named(text) returns person
as 'select * from person where name = $1'
language 'sql';
CREATE

insert into book values ('ma vie', person_named('nicolas'));
INSERT 692347 1

insert into book values ('mon fils', person_named('nicolas'));
INSERT 692348 1

insert into book values ('mon pere', person_named('julien'));
INSERT 692349 1

select * from book where name(author) = 'nicolas';
NOTICE:  An operand to the '=' operator returns a set of text,
ERROR:  but '=' takes single values, not sets.

create function is_person_named(person,text) returns bool
as 'select $1.name = $2'
language 'sql';
CREATE

select * from book where is_person_named(author, 'nicolas');
PQexec() -- Request was sent to backend, but backend closed the channel
before responding.
        This probably means the backend terminated abnormally before or
while processing the request.

On the other hand, when I switch back to the terminal from which I sent postmaster, there is a message :
NOTICE : Message from PostgreSQL backend:
        The Postmaster has informed me that some other backend died abnormally and possibly corrupted shared memory.
        I have rolled back the current transaction and am going to terminate your database system connection and exit.
        Please reconnect to the dsatabase system and repeat your query.

As far as I am concern, I just want to define a table with a column of another table, insert rows in it, and select
 rows with this column having particular attribute values (like : select * from book where author.name = 'nicolas';).

The process described above shows me that it not easy to do this.
The abortion of the process during the last query appears to be anything but normal, am I wrong ?

I'm running PostgreSQL V6.3.2 on a linux box with a 2.0.30 kernel.

Can someone tell me more ? Am I sending in the right list ?

Thank you all

Nicolas

--
Nicolas Caillaud
Nicolas.Caillaud@mail.dotcom.fr






Message-ID: <358451C9.FFB49170@mail.dotcom.fr>
Permalink:  ../358451C9.FFB49170@mail.dotcom.fr/
Also on:    postgresql.org/message-id/358451C9.FFB49170@mail.dotcom.fr

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: Nicolas.Caillaud@mail.dotcom.fr
  Subject: Re: Table with attributes of complex type (again)
  In-Reply-To: <358451C9.FFB49170@mail.dotcom.fr>
  References: <358451C9.FFB49170@mail.dotcom.fr>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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