[linux-l] SQL / PostgreSQL

[Steffen Dettmer]

* Olaf Radicke wrote on Tue, Sep 19, 2006 at 19:56 +0200:
> Ich habe ein Problem...
> Wenn ich eine Datensatz in eine Tabelle einfüge, dessen einziger eindeutiger 
> Schüssel ein SERIAL PRIMARY KEY ist, wie bekomme ich raus, unter welcher ID 
> der Datensatz gespeichert wurde? Nachträglich danach suchen geht nicht, weil 
> alle anderen Spalten mehrfach vorkommen können und nicht eindeutig sind. Also 
> brauche ich gleich beim INSERT die SERIAL PRIMARY KEY zurück. Wie bekommt man 
> das hin??

SERIAL ist sowas wie DEFAULT nextval('my_sequence_name'), macht intern
auch genau sowas.

Abfragen mit select currval('my_sequence_name'):

http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html

Manchmal braucht man sowas wie "select or create", das ist ein feines
Beispiel, finde ich.

Kann man z.B. so machen:

-- Function to find or create a address record for given mobile
--   number and customer. The reply_count will be incremented and
--   the advertising flag updated.
--   PARAMETERS
--     addressp:         mobile phone number that sent reply
--     customer_idp:     customer id of this ad_action
--     new_advertisingp: new value for advertising flag
--   RETURNS 
--     The address ID of (may have been created freshly)
CREATE OR REPLACE FUNCTION get_reply_address_id (
    varchar,     -- address of the SMS sender for address_id
    int,         -- Customer-ID
    boolean      -- new advertising flag
) RETURNS int AS '

  DECLARE
--- CUT 6 lines ---
  BEGIN
--- CUT 54 lines ---

    -- otherwise, create new record
    INSERT INTO address
      ( address_group_id, receive_id, reply_count,
        customer_id, a_nickname, advertising )
    VALUES
      ( ''none'', addressp, 1,
        customer_idp, ''auto_created'', new_advertisingp);
    RAISE NOTICE ''get_reply_address_id: created %'', addressp;
    SELECT INTO address_idv
      currval(''address_address_id_seq'');
    IF FOUND THEN
      RAISE NOTICE ''get_reply_address_id: created %'', address_idv;
      RETURN address_idv;
    END IF;

    RAISE EXCEPTION ''get_reply_address_id: internal error, creation failed!'';

  END;
' LANGUAGE 'plpgsql';

und dann aufrufen:

select get_reply_address_id ('1708134484', 24, true);

(Beim zweiten cut wird gesucht und SELECT FOR UPDATE gemacht und bei
Erfolg returned - SELECT - sonst halt erzeugt - or CREATE).

Die Sequence lege ich lieber explizit an:

CREATE SEQUENCE address_address_id_seq;
CREATE TABLE "address" (
   "address_id" int4 DEFAULT nextval('"address_address_id_seq"') NOT NULL,
   --- CUT ---
);

damit heisst er immer korrekt. SERIAL ist mir zu automagisch, macht aber
auch nur sowas wie DEFAULT nextval('tabelle_feld_id_seq') NOT NULL oder
was sehr ähnliches. Das das so Mist ist, merkt man, wenn man noch so
ein Feld anlegen will nach einem Rename, weil die Sequence dann
plötzlich gleich heisst.

Nett ist auch, wenn man wenig Sequencen für viele Keys nimmt, so kann
man übergreifend eindeutige Ids vergeben - gut für's debuggen (ID
12412341 ... aha, ein Kunde und kein Auftrag - oder sowas). curval und
nextval verhalten sich dann natürlich korrekt und nicht unbedingt wie
erwartet :-)

oki,

Steffen

-- 
Dieses Schreiben wurde maschinell erstellt,
es trägt daher weder Unterschrift noch Siegel.