pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ansley, Michael <Michael.Ansley@intec.co.za>
To: 'Andreas Theofilu' <theofilu@eunet.at>
To: Michael Meskes <meskes@postgreSQL.org>
Cc: pgsql-interfaces@postgreSQL.org
Subject: RE: [INTERFACES] ecpg and getting just assigned serial number
Date: Mon, 13 Sep 1999 14:56:40 +0200
Message-ID: <1BF7C7482189D211B03F00805F8527F748C06B@S-NATH-EXCH2> (raw)

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?

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.

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

MikeA


>> -----Original Message-----
>> From: Andreas Theofilu [mailto:theofilu@eunet.at]
>> Sent: Monday, September 13, 1999 2:11 PM
>> To: Michael Meskes
>> Cc: theofilu@eunet.at; pgsql-interfaces@postgreSQL.org
>> Subject: Re: [INTERFACES] ecpg and getting just assigned 
>> serial number
>> 
>> 
>> Am Mo, 13 Sep 1999 schrieben Sie:
>> > On Sun, Sep 12, 1999 at 04:25:17PM +0200, Theofilu Andreas wrote:
>> > > I'm using 'ecpg' to develop my application, because it's 
>> mostly compatible
>> > > to Informix's 'esqlc'. However. I have a table with a 
>> field of type
>> > > 'serial'. Now I've no problem to insert any sentences 
>> into this table, but
>> > > immediately after inserting a new sentence I need the 
>> newly assigned number
>> > > to the serial field in the table. How can I access this number?
>> > > With Informix I got this number in a field of structure 
>> 'sqlca'. With
>> > > PostgreSQL the same field in this structure exists but 
>> contains always 0,
>> > > as documented.
>> > 
>> > Where exactly does Informix return it? sqlca[?].
>> 
>> Informix returns this number in 'sqlca.sqlerrd[1]'. 
>> Currently this field
>> is not used by ecpg.
>>   
>> > Also does anyone know whether the backend returns that 
>> number somewhere?
>> 
>> Now I know that the backend does not return this number 
>> anywhere. Instead
>> you need two SQL commands to insert a sentence and get the 
>> number. You can
>> do it with following commands:
>> 
>>    EXEC SQL nextval ('<sequence name>') into :variable;
>>    EXEC SQL insert into <table> values (:variable, ...);
>> 
>> Maybe ecpg can implement the first call behind the scenes 
>> and put the new
>> serial number into 'sqlca.sqlerrd[1]'.
>> In Informix I have to write:
>> 
>>    EXEC SQL insert into <table> values (0, ...);
>> 
>> The '0' increments the serial counter, who is a simple int4 
>> field in a
>> system table, by one, inserts the sentence with new serial 
>> number into the
>> table and returns the new number in 'sqlca.sqlerrd[1]'.
>> It would be nice to have this feature exactly the same way 
>> with 'ecpg'.
>> This would make it more compatible, at least against 
>> Informix. Don't know
>> about Oracle.
>> 
>> BTW: When I insert a sentence in Informix and the serial 
>> field is not 0,
>> but contains a unique number and this number is higher than 
>> the current
>> value of the serial counter, the serial counter is set to 
>> the manualy set
>> number. This is important if one inserts sentences from a file. For
>> example when I move data between two machines/databases. 
>> With PostgreSQL
>> I've to set the counter into the sequence by hand, or it will not be
>> incremented.
>> --
>> Theofilu Andreas
>> http://members.eunet.at/theofilu
>> 
>>                -------------------------------------------------
>>                            Enjoy the science of Linux!
>>                        Genieße die Wissenschaft von Linux!
>>                -------------------------------------------------
>> 
>> ************
>> 



view thread (4+ messages)  latest in thread

Message-ID: <1BF7C7482189D211B03F00805F8527F748C06B@S-NATH-EXCH2>
Permalink:  ../1BF7C7482189D211B03F00805F8527F748C06B@S-NATH-EXCH2/
Also on:    postgresql.org/message-id/1BF7C7482189D211B03F00805F8527F748C06B@S-NATH-EXCH2

 · 

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: Michael.Ansley@intec.co.za, theofilu@eunet.at, meskes@postgreSQL.org, pgsql-interfaces@postgreSQL.org
  Subject: Re: [INTERFACES] ecpg and getting just assigned serial number
  In-Reply-To: <1BF7C7482189D211B03F00805F8527F748C06B@S-NATH-EXCH2>
  References: <1BF7C7482189D211B03F00805F8527F748C06B@S-NATH-EXCH2>

* 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