PSQL-Programmierung mit LOOP und IF

Hallo zusammen,

ich möchte meine Kenntnisse im Bereich PSQL erweitern und möchte folgendes in JDBC realisieren:

Mein Projekt ist eine Kundendatenbank. Die Kundennummern werden beim Anlegen eines neuen Kunden, von 1 aufwärts, fortlaufend vergeben. Wenn ein Kunde aus der Datenbank gelöscht wird, kann seine Nummer neu vergeben werden.

Die For-Schleife in getIndex() soll dann solange laufen bis die „Lücke“ gefunden wurde und diese Zahl dann zurückgeben oder die nächsthöhere Zahl zurückgeben, wenn alle Zahlen belegt sind. Also beispielsweise 1,2,3,4 gibt es schon, dann wird die 5 gewählt. Die Funktion checkEx(INTEGER) soll für jede Zahl prüfen ob diese schon vergeben ist.

Ich habe euch die relevanten Methoden einmal kopiert. Leider funktioniert das Ganze noch nicht so ganz. Ich vermute, dass es an der For-Schleife liegt.

Ich bin für jeden Tipp dankbar. :slightly_smiling_face: :sleepy:

public static void createFunctions() throws SQLException {

stmt = conn.createStatement();

// checkEx(INTEGER)
	String sqlCheckEx = "CREATE OR REPLACE FUNCTION checkEx(INTEGER) RETURNS 
       INTEGER AS $BODY$ " +

		"DECLARE " +
		
			"ct INTEGER; " +
		
		"BEGIN " +
			   
			"PERFORM COUNT(1) INTO ct "      + 
			"FROM kunde "                            +        	      
			"WHERE unique_key = $1; "            +
		   
		    "RETURN ct; "             + 
		   
		"END; " +
	
	"$BODY$ LANGUAGE plpgsql; " ; 
	
	stmt.executeUpdate(sqlCheckEx);
	
// getIndex()
String sqlIndex = "CREATE OR REPLACE FUNCTION getIndex() RETURNS INTEGER AS $BODY$ " +
	
						
	"DECLARE " +

	   "inx INTEGER   := 1; "                          +
	   "check INTEGER := 0; " +
	
					 
	"BEGIN " + 
			
				"FOR  i IN 1.." + getAnzKd() + " LOOP "              +
				   
				//"check := checkEx(i); "         				     + 

				  "PERFORM 'SELECT checkEx(i) INTO check'; " + 

							   "IF     check=1  THEN     inx := i; "                         +
						       "ELSE                     inx := " + (getAnzKd()+1) + "; "   +
						       "END IF; "  + 

// "IF check=1 THEN PERFORM 'SELECT i INTO inx; " +
// "ELSE PERFORM 'SELECT (getAnzKd()+1) INTO inx; " +
// "END IF; " +

				 "END LOOP; "                                              +
				 		
			"RETURN inx; "                            +
			
	"END; " +
                                      
		
	
"$BODY$ LANGUAGE plpgsql; ";

stmt.executeUpdate(sqlIndex);


IO.println("\n\n--> Funktionen erstellt");

}

public static boolean addKunde() throws SQLException {
	
	
	IO.println("Kunde hinzufuegen");
	String tempVorname, tempNachname;

	IO.print("Vorname: ");  tempVorname = IO.inputString();
	IO.print("Nachname: "); tempNachname = IO.inputString();

	Kunde kunde = new Kunde(getKdNr(), tempVorname, tempNachname);
	
	return addKunde(kunde);
}

public static int getAnzKd() throws SQLException {
	String sql = "SELECT COUNT(1) FROM kunde";
	ResultSet rs = stmt.executeQuery(sql);
	rs.next();
	return rs.getInt(1);
}


public static int getKdNr() throws SQLException {
	

String sql = "SELECT * FROM getIndex(); ";
	
		
	ResultSet rs = stmt.executeQuery(sql);
	rs.next();
	System.out.println("--->" + rs.getInt(1));	
	return rs.getInt(1);
}

public static boolean addKunde(Kunde kunde) throws SQLException {

	
	stmt = conn.createStatement();
	
	String sql = "INSERT INTO Kunde VALUES ('" + kunde.KDNR + "', '" + kunde.vorname + "', '" 
            + kunde.nachname + "')";
	
	//SQL-Befehl ausgeben
	IO.printSQL(showSQL, sql);
	
	return stmt.executeUpdate(sql) == 1 ? true : false;
}

Gaaaanz schlechte Idee. Die Kundennummern willst Du sicherlich auch in anderen Tabellen dann als Foreign Key verwenden, stimmts?

Aber davon eigentlich sogar unabhängig: die Such nach einer freien ‘Lücke’ in den Nummern ist in einem Multi-User-System nicht trivial und extrem teuer. So eine Kundennummer hat auch keine Bedeutung für die Anwendung / den Kunden, sie dient nur zur eindeutigen Identifizierung.

test=# create table kunden (nummer int generated always as identity primary key, name text);
CREATE TABLE
test=*# create table bestellung (kunden_nummer int references kunden, betrag int);
CREATE TABLE

Damit hast Du eine automatisch generierte Kundennummer, die eindeutig ist.

Danke für die schnelle Antwort.
Ich kann mir gut vorstellen dass mein Ansatz in der Praxis quatsch ist. Zum Glück arbeite ich nur an einem Übungsprojekt. :grinning: Mir ging es darum das Arbeiten mit Schleifen und IF-Abfragen etc. in PSQL zu erlernen.

Mich würde trotzdem interessieren was am code falsch ist, da ich schon eine Menge Zeit und Energie darin investiert habe :grinning:

Ja, würde ich. Wäre das denn ein Problem?

Dein PERFORM verwirft das Resultat und liefert nix zurück, das dürfte dasProblem sein.

Sometimes it is useful to evaluate an expression or SELECT query but discard the result, for example when calling a function that has side-effects but no useful result value. To do this in PL/pgSQL, use the PERFORM statement:

Das Hauptproblem ist, daß Dir das ganze bei etwas größeren Datenmengen um die Ohren fliegt. Hint: keine SQL’s in Schleifen.

Den (falschen) Ansatz Werte von Primärschlüsseln wieder zu verwenden hat akretschmer ja schon kommentiert.

Der Ansatz in “Schleifen” zu denken ist im Kontext von SQL Abfragen fast immer falsch. SQL ist eine mengenorientierte Abfragesprache, Schleifen und IF Bedingungen kommen da nicht vor.

Funktionen sollten auch nicht via JDBC angelegt werden, dass ist ganz schlechter Programmierstil. Der Code für eine Funktion (oder Prozedur) sollte als SQL Skript abgelegt werden und genauso wie der Java Quellcode in einer Source-Code Verwaltung (z.B. git) abgelegt werden. Teil der Installationsprozedur für die Anwendung ist dann das Ausführen aller SQL Skripte um die Datenbank in einen definierten Zustand zu bringen.

Zu den Funktionen selber (unabhängig davon dass der Ansatz an sich falsch ist).

Parameter sollten einen Namen bekommen, damit man diese nicht mit $1, $2 ansprechen muss. Das erhöht die Lesbarkeit des Quellcodes substantiell.

checkex() sollte dann so aussehen:

CREATE OR REPLACE FUNCTION checkex(p_id integer) 
  RETURNS INTEGER 
AS
$BODY$
DECLARE
 ct INTEGER;
BEGIN
  select count(*) 
    INTO ct 
  FROM kunde
  WHERE unique_key = p_id;
  RETURN ct;
END; 
$BODY$ LANGUAGE plpgsql; 

Auch sollte die Anzahl der Kunden nicht im Quellcode fest verdrahtet werden (wie Du es implizit beim Erstellen der Funktion machst), sondern sollte dynamisch zur Laufzeit erfolgen. Z.B. durch eine FOR schleife über eine SELECT Abfrage über die Kundentabelle. Wenn Du das Resultat nach dem unique_key sortierst, dann kannst Du einfach prüfen, ob eine “Lücke” vorhanden ist.

CREATE OR REPLACE FUNCTION getindex() 
  RETURNS INTEGER 
AS 
$BODY$  
DECLARE  
  inx INTEGER := 0;
  l_rec record;
BEGIN   
  FOR  l_rec IN SELECT unique_key FROM kunden ORDER BY unique_key
  LOOP
    -- wenn der aktuelle unique_key nicht genau eins größer ist 
    -- als der "letzte" dann haben wir eine Lücke gefunden
    if l_rec.unique_key <> inx + 1 then
      return inx + 1;
    end if;
    inx := l_rec.unique_key;
  END LOOP;
  RETURN inx + 1; 
END;
$BODY$
language plpgsql;

Damit wird auch die Funktion checkex() überflüssig.

Grundsätzlich kann man das auch mit einer einzigen SQL Abfrage machen indem man sog. Window Funktionen verwendet die “mitzählen” und jedem Datensatz im Ergebnis so einen eindeutigen Nummer geben können.

select unique_key, row_number() over (order by unique_key) idx
from kunden
order by unique_key;

Das kann man dann verwenden den ersten Datensatz herauszufiltern bei dem idx nicht gleich dem unique_key ist:

select idx
from (
  select unique_key, row_number() over (order by unique_key) idx
  from kunden
) t
where unique_key <> idx
order by unique_key
limit 1;

Das order by unique_key limit 1 sorgt dafür, dass die erste gefundene (fehlende) Nummer zurückgegeben wird.

Aber wie gesagt: der ganze Ansatz ist eine wirklich schlechte Idee und sollte auf keinen Fall verwendet werden.

1 „Gefällt mir“

P.S.: Im übrigen ist count(1) in Postgres tatsächlich etwas langsamer als count(*) auch wenn vielen Seiten im Internet das Gegenteil behaupten (es gibt kein einziges Datenbanksystem bei dem count(1) schneller ist)

Danke erstmal für deine ausführliche Antwort :+1:

Mein Code sieht jetzt folgendermaßen aus.

        public static int getKdNr() throws SQLException {


String sql = "SELECT * from getIndex(); ";
	
		
	ResultSet rs = stmt.executeQuery(sql);
	rs.next();
	return rs.getInt(1);
}

public static void createFunctions() throws SQLException {

stmt = conn.createStatement();
		
String sqlIndex = "CREATE OR REPLACE FUNCTION getindex() " + 
		  "RETURNS INTEGER " +
		  "AS "+
		  "$BODY$ "  +
		  "DECLARE "  +
		    "inx INTEGER := 0; "+
		    "l_rec record; "+
		  "BEGIN "   +
		    "FOR  l_rec IN SELECT unique_key FROM kunde ORDER BY unique_key " +
		    "LOOP " +

		      "if l_rec.unique_key <> inx + 1 then " +
		        "return inx + 1; " +
		      "end if " +
		      "inx := l_rec.unique_key; " +
		    "END LOOP; " +
		    "RETURN inx + 1; " + 
		  "END; $BODY$ " +
		  "language plpgsql; ";

 stmt.executeUpdate(sqlIndex) == 1 ? true : false;

}

Kann es sein dass unten ein $BODY$ gefehlt hat ? Ich bekomme leider immer noch Fehlermeldungen dass die Syntax nicht in Ordnung ist :pensive: Ich hätte gerne noch ein Erfolgserlebnis bevor ich den Ansatz in die Tonne werfe :grinning: :grinning:

Du hast Recht, das $BODY$ hat gefehlt, aber auch ein ; nach dem END IF gefehlt.

Online Beispiel

Aber wie schon gesagt: sowas sollte man nie aus der Anwendung heraus machen, sondern immer in die Datenbank-Setup Skripte integrieren. Wenn man das mit einem SQL Tool macht, ist auch die Fehlersuche wesentlich einfacher und geht schneller