Skip site navigation (1) Skip section navigation (2)

RE: [INTERFACES] ecpg and getting just assigned serial number

From: Andreas Theofilu <theofilu(at)eunet(dot)at>
To: pgsql-interfaces(at)postgreSQL(dot)org
Cc: theofilu(at)eunet(dot)at
Subject: RE: [INTERFACES] ecpg and getting just assigned serial number
Date: 1999-09-13 13:46:51
Message-ID: 99091316123503.01664@theofilu (view raw or flat)
Thread:
Lists: pgsql-interfacespgsql-sql
Am Mo, 13 Sep 1999 schrieben Sie:
> It's would be pretty difficult to return the unique key, because that is
> defined by the user, and what if I had a table into which were placed values
> from two separate sequences (not great design, but possible), or a compound
> unique key, or multiple unique keys?  The things that's necessary here is
> for the sqlca struct to contain the oid of the newly inserted row after the
> insertion.  Of course, then there is a problem if more than one row is
> inserted.  What does Informix do then?

Agree. It's difficult to do that with PostgreSQL. It works with Informix
only because the type 'serial' is realy a seperate type and handled
completely different. Beside this it's allowed to have only one field of
type serial in a table. With SQL command 'insert' you can only insert one
sentence at a time. To enter more than one sentence you must program a
loop. So I can see no problem with the serial number. Beside this: The
serial field of Informix is independant of any index. Therefore it doesn't
matter how complicate an index may be.

> But why can't you just write a database function that performs the insert,
> and returns the value, and then call the function using embedded sql.  The
> function can be written in plsql, and accept as parameters all the values
> that need to go into the new record.  This is far better db design.

I can write such a function, of course. But I will not break compatibility
to Informix, because both databases, PostgreSQL _and_ Informix are
supported in only a single source file.

> The basic function looks like this (pseudocode):
> 
> BEGIN
> 	get next sequence value into i;
> 	insert new record using parameters and i;
> 	if insert fails then
> 		return (0);
> 	else
> 		return (i);
> END
> 
> This has the added benefit of being able to add further business rules into
> the function, which better encapsulates your process.  Of course, this is
> design dependent, but it's a method I've used quite successfully in the past
> (although, admittedly, not in PG ;-)

I've done exactly the same your example shows, but not in a seperate
function. I simply added one SQL command to get the next number out of the
sequence. This works well now and the extra line is ignored when compiling
for Informix :-).
--
Theofilu Andreas
http://members.eunet.at/theofilu

               -------------------------------------------------
                           Enjoy the science of Linux!
                       Geniee die Wissenschaft von Linux!
               -------------------------------------------------

In response to

Responses

pgsql-interfaces by date

Next:From: Kevin MulhollandDate: 1999-09-13 14:53:48
Subject: subscribe
Previous:From: Roberto Joao Lopes GarciaDate: 1999-09-13 13:15:59
Subject: Re: [INTERFACES] Can PostgreSQL be used in a C++ application

pgsql-sql by date

Next:From: Michael RichardsDate: 1999-09-13 14:49:46
Subject: Re: [BUGS] Running queries on inherited tables
Previous:From: Ansley, MichaelDate: 1999-09-13 12:56:40
Subject: RE: [INTERFACES] ecpg and getting just assigned serial number

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group