"Serial" Spalte auslesen in "AFTER INSERT" Trigger

Hallo zusammen,
In einer Tabelle (“test_tab”) soll es u.a. eine Spalte “tab_id” vom Typ SERIAL geben. In einem “AFTER INSERT” Trigger möchte ich gerne den Wert ermitteln den die Spalten “tab_id” bekommt.

new.tab_id enthält zu diesem Zeipunkt den Wert 0

Wenn ich SELECT currval(‘seq_name’) verwende, dann bekomme ich die Fehlermeldung:
“currval of sequence ‘seq_name’ ist not yet defined”.

SELECT currval(pg_get_serial_sequence(‘test_tab’,‘tab_id’)) führt zum gleichen Fehler.

Auch SELECT lastval() hilft hier nicht weiter, da man hier keinerlei Kontrolle darüber hat, welchen Wert man von welcher Sequence man bekommt.
Zu guterletzt kommt auch “SELECT last_value FROM seq_name” nicht in Frage, auch hier hat man keine Kontrolle darüber, ob man tatsächlich den richtigen Wert bekommt.

Gibt es noch irgendeine Möglichkeit im “AFTER INSERT”-Trigger gesichert und definiert an den (zukünftigen) Wert von tab_id zu kommen? Ich konnte sonst nichts finden.

Vielen Dank

viele Wege führen zum Ziel …

edb=# create table nachtvogel(id serial, data text);
CREATE TABLE
edb=*# create trigger trg1 after insert on nachtvogel for each row execute procedure get_id();
CREATE TRIGGER
edb=*# 
edb=*# 
edb=*# insert into nachtvogel (data) values ('test 1');
HINWEIS:  neue id: 1i
INSERT 0 1
edb=*# insert into nachtvogel (data) values ('test 1') returning id;
HINWEIS:  neue id: 2i
 id 
----
  2
(1 row)
edb=*# with x as (insert into nachtvogel (data) values ('test 1') returning id) select x.id from x;
HINWEIS:  neue id: 3i
 id 
----
  3
(1 row)

edb=*# 

Wie Du siehst, funktioniert der TRIGGER, ist aber unnötig. currval() funktioniert, aber nur nach einem erfolgten Insert in dieser Session.

So weit war das klar. Ich bleib mal bei Deinem Beispiel.

Ich brauche den Wert von “id” nicht in der Tabelle nachtvogel. Das läuft automatisch, keine Frage.
Ich brauche den Wert, um damit einen Log Eintrag in einer anderen Tabelle zu generieren.

Und meine Hoffung war, dass zum Zeitpunkt, wenn der “after insert” Trigger zündet, der Wert irgendwie zugänglich ist.
Das scheint nicht der Fall zu sein.

Dann bleibt wohl nichts anderes als in guter alter ORACLE- Manier (da komme ich her) einen “before insert” zu nehmen, auf “serial” zu verzichten, die sequence selber abzufragen und damit meine Einträge zu machen.

Aber danke für die Antwort

edb=*# create table andere_tabelle (nachtvogel_id int, data text);
CREATE TABLE
edb=*# with x as (insert into nachtvogel (data) values ('test 3') returning id) insert into andere_tabelle select id, now()::text from x;
HINWEIS:  neue id: 4i
INSERT 0 1
edb=*# table nachtvogel;
 id |  data  
----+--------
  1 | test 1
  2 | test 1
  3 | test 1
  4 | test 3
(4 rows)

edb=*# table andere_tabelle ;
 nachtvogel_id |              data              
---------------+--------------------------------
             4 | 17-JUN-21 16:29:54.1146 +02:00
(1 row)

Der TRIGGER ist und bleibt unnötig, viel einfacher geht das wie gezeigt, nennt sich wCTE (writeable Common Table Expression)

1 „Gefällt mir“

interessante Konstruktion und in jedem Fall hab ich eine Menge gelernt.

Mein Großprojekt ist es, bei einer sehr lange bestehenden Datenbankanwendung die Datenbank von Oracle nach Postgres zu überführen. Mal sehen, ob ich das so anwenden kann, ob das alles in die bestehenden Strukturen paßt.

Herzlichen Dank für die Mühen

Du kannst Dir auch mal den Advanced PostgreSQL Server von EDB anschauen - der hat eine Ora-Kompatibilität. Ist aber kein OpenSource mehr. Bei Interesse kontaktiere mich direkt.

Über den new record geht das einwandfrei.

Ich vermute bei Deiner Triggerdefinition ist was falsch.

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=fd0d6f312851098cdd1c6068a05a1797

Das ganze ist noch viel simpler. Manchmal steht man einfach auf dem Schlauch:
meine Eingangs geschilderten Probleme kommen daher, dass mit dem INSERT versehenlich einen Wert für die ID mitgegeben hab, dann wird natülich keine neue ID ermittlet.
Sorry für die Umstände

Dann hättest Du aber zumindest die ID die übergeben worden ist “sehen” müssen.

Das ist übrigens einer der Gründe warum man generated always as identity (statt serial) verwenden sollte in modernen Postgres Versionen - dann kannst Du nicht “versehentlich” die Sequence Generierung umgehen.

1 „Gefällt mir“

Das Guten an Fehlern - und seien sie noch so blöd - ist, dass man meist eine Menge lernt. Ich beanspruche noch Welpenschutz

@akretschmer: auch wenn es über mein ursprüngliches Problem hinausgeht, drängt sich mir eine Frage auf. Ganz offensichtlich bist Du kein großer Freund von Triggern. Jetzt würde mich doch interessieren, warum nicht? Die von Dir aufgezeigte Möglichkeit sieht chick aus und ich werde das auch ausprobieren. Im Moment ist für mich aber nicht ganz klar wo die Vor- bzw. Nachteile liegen.Läßt sich das in kurzen Worten sagen?

TRIGGER benötigen mehr Ressourcen als die gezeigte Lösung. Ansonsten habe ich nix gegen Trigger, nur warum wenn es auch einfacher geht?

Ich finde Trigger sind in einer DB ungefähr die intransparentesten Objekte, die man nutzen kann. Dementsprechend (selten) würde ich sie für (klare) Aufgaben nutzen. Z.B. als erstes und einziges Schlüssel und Fremdschlüsselwerte vergeben. Also einfach auf der Konsistenzebene einsetzen.
Wenn es um “Große (Oracle) Projekte” geht, könnte es sein, dass ein heterogener Zugriff erfolgt, also kein Appserver o.ä. Dann wäre das auf jeden Fall ein Argument für Trigger, die Keys oder FK einheitlich vergeben, statt einmal wie Akretschmer und im nächsten Client irgendwie anders.

Dieses Thema wurde automatisch 60 Tage nach der letzten Antwort geschlossen. Es sind keine neuen Antworten mehr erlaubt.