Insert v PostgreSQL 7.1.3

[David Zabensky]

Pavel Tyrner wrote:

> Dobry den,
> prosim o radu pro tento pripad:
> Mam tabulku, kde je m.j. pole ID, ktere je serial a tudiz se pri INSERTu
> zaznau do tabulky automaticky zvysuje jeho hodnota o 1. To vse funguje k me
> radosti v pohode :o).
> Potrebuji ale hned v nasledujicim kroku znat hodnotu onoho pridaneho ID. Da
> se to nejak vycist hned po prikazu "INSERT INTO.......", nebo musim delat
> nasledne jeste "SELECT ID ..... ORDER BY ID DESC LIMIT 1", cimz zjistim
> nejvysii (a tudiz posledni) hodnotu ID? Z tohoto SELECTU mam totiz obavy: co
> kdyz ve stejnou chvili (je to malo pravdepodobne, ale co kdyz...) vlozi do
> tabulky jeste nekdo dalsi zaznam, pak mi je onen SELECT na nic.....
> S diky za radu
> Pavel Tyrner
> http://www.czprima.cz
> 
> 
> 
> 

Dobry den,

typ serial je vzdy svazany s nejakou sekvenci, jenz slouzi jako generator hodnot
daneho sloupce tabulky. Vice se o nich doctete v dokumentaci pgsql, prikaz

'CREATE SEQUENCE':

--
CREATE SEQUENCE will enter a new sequence number generator into the current data base. This involves 
creating and initializing a new single-row table with the name seqname. The generator will be owned 
by the user issuing the command.

After a sequence is created, you may use the function nextval('seqname') to get a new number from 
the sequence. The function currval('seqname') may be used to determine the number returned by the 
last call to nextval('seqname') for the specified sequence in the current session. The function 
setval('seqname', newvalue) may be used to set the current value of the specified sequence. The next 
call to nextval('seqname') will return the given value plus the sequence increment.
--

pokud si date describe tabulky v psql (\d <tabulka>), uvidite kterou sekvenci pgsql vyuziva
(jmeno je stejne jako <tabulka>_<sloupec>_seq). Pote muzete v selectech ci insertech pouzivat
currval(), nextval()... nad touto sekvenci.


DZ