SQL Funktion mit INSERT statements und Exceptions

Hallo pg community,
ich möchte eine SQL Funktion erezugen, die bei Ausführung folgendes leistet:

  • Ausführung von n INSERT statements
  • Prüfung, ob ein INSERT statement erfolgreich war
  • Bei Fehler soll eine Exception ausgegeben werden


    Folgender Hintergrund:
    Zum Zeitpunkt x wird die Funktion (temporär) als Transaction erzeugt. Ein XML file wird per XSD in viele INSERT commands übersetzt und in diese Funktion geschrieben. Mir ist momentan nicht klar, wie ich innerhalb dieser Funktion prüfen kann, ob ein INSERT erfolgreich war. Ich habe dies bereits per if then else und RETURNING versucht, allerdings scheint der if-Block keine INSERT Funktion als Argument zuzulassen.
    Ist die Funktion angelegt, soll sie zu einen späteren Zeitpunkt ausgeführt werden.

Hier ein Beispiel:

CREATE OR REPLACE FUNCTION “db”.importprogram()
RETURNS boolean As $BODY$
BEGIN

if (
INSERT INTO
program(col1, col2, col3)
VALUES
(val1, val2, val3) RETURNING col1
) > 0 then

return true;
else
RAISE EXCEPTION ‘Insertion of new program has failed…’;
return false;
end if;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION “db”.program()
OWNER TO db;

PostgreSQL: Documentation: 9.1: Control Structures

Generell ist alles, was innerhalb einer Funktion passiert, durch die Funktion selbst als eine Transaktion definiert. Du mußt da nicht extra nach jedem Insert nachschauen.
Viele Inserts gehen schneller wenn Du stattdessen COPY nimmst.

Andreas

Hallo Andreas,

danke für die Antwort. Die copy Variante werde ich prüfen.

Zum Thema Prüfung per INSERT:

  • die INSERT Befehle wirken sich nicht jeweils auf die selbe Tabelle aus, es gibt verschiedene Typen
  • ich möchte nach jedem INSERT nachschauen, damit ich weiss, an welcher Stelle der Fehler auftrat und bei welchem Typ (dazu dann ein errorcode je nach Typ)
  • der Anwender sollte dann per Exception darauf hingewiesen werden, an welcher Stelle er einen falschen Eintrag getätigt hat

Ich habe herausgefunden, dass die Variable ‘found’ die erfolgreiche Ausführung von INSERT, SELECT, UPDATE speichert.
“A SELECT INTO statement sets FOUND true if a row is assigned, false if no row is returned.”
“UPDATE, INSERT, and DELETE statements set FOUND true if at least one row is affected, false if no row is affected.”

Problem ist dabei jedoch, dass pg beim INSERT nicht bis zur Exception läuft, sondern schon davor eine Fehlermeldung wirft. Bei SELECT funktioniert es.

mb

Das geht schon auch, mußt halt die Exception abfangen.

test=# create table mbpg (i int check (i between 1 and 10));
CREATE TABLE
test=*# create or replace function insert_mbpg(int) returns bool as $$begin insert into mbpg(i) values ($1); return true; exception when check_violation then raise notice 'dat war nix'; return false; end; $$language plpgsql;
CREATE FUNCTION
test=*# select insert_mbpg(1);
 insert_mbpg
-------------
 t
(1 row)

test=*# select insert_mbpg(11);
NOTICE:  dat war nix
 insert_mbpg
-------------
 f
(1 row)

Andreas

Fange die exception mit OTHERS ab.
Zusätzlich hätte ich gerne die Info, welches meiner INSERTS betroffen ist. Wie bekomme ich das RAISE NOTICE Event auf den Client?
Ich arbeite mit VB.NET und npgsql.



test=*# create or replace function insert_mbpg(int) returns bool as $$begin insert into mbpg(i) values ($1); return true; exception when check_violation then raise notice 'dat war nix %',$1; return false; end; $$language plpgsql;
CREATE FUNCTION
test=*# select insert_mbpg(12);
NOTICE:  dat war nix 12
 insert_mbpg
-------------
 f
(1 row)



Wie bekomme ich das RAISE NOTICE Event auf den Client?
Ich arbeite mit VB.NET und npgsql.

Sorry, damit kenn ich mich ned aus…

Andreas