Werte einer Tabelle in einen Loop überführen

[Hannes]

Hallo zusammen, ich stehe mal wieder auf dem Schlauch. Dieses Mal soll ich anhand der Werte einer Tablle jeweils eine Abfrage durchlaufen lassen, um dananch eine Gesamttabelle zu erhalten

Ich hatte mir vorgestellt, das über einen Loop mit PL/pgSQL durchzuführen - Premiere für mich. So richtig will mir das Vorgehen nicht einleuchten. Das Prinzip habe ich schon verstanden, denke ich.
Leider exerzieren die meisten Tutorials das Ganze nur mit RAISE NOTICE durch, was mir bei meinem komplexeren Thema nicht gerade weiterhilft.

-----------------------------------------------------

A) Ausgangspunkt ist meine Tabelle1. Die Iteration sollte auf der id laufen. Im ersten Schritt wären die x, y-Werte gefragt. In einem 2. Schritt müsste ich auf die Spalte ks bezug nehmen.

Tabelle1
id | ks | x | y
---+----+---+--
1 | 26 |52 |13
2 | 11 |51 |12
3 | 8 |53 |11

Tabelle_neu (noch leer)

ks_neu | var1 | var2 | var3
-------+------+------+-----
| | |

B) Außerdem habe ich ein Abfrage mit einer Funktion Select * from x-y-funktion (x, y) (die Funktion selektiert über mehrere Tabellen Daten, was an dieser Stelle nicht so wichtig ist - es kommt eine einfache Tabelle dabei heraus)

---------------------------------------------------

Schritt 1:
Die Werte x, y aus Tabelle1 sollen im ersten Schritt in die Funktion x-y-funktion (x, y) übernommen werden und als Ergebnis sollen jeweils die mit der Funktion abgefragten Werte in die Tabelle_neu geschrieben werden.

Insert into tabelle_neu (var1, var2, var3)
    Select * from x-y-funktion (x, y)

Schritt 2:
In die Tabelle_neu müssten anschließend die NULL-Werte für ks_neu mit den jeweiligen ks-Werten aus der Tabelle1 ergänzt werden.

update Tabelle_neu 
    set ks_neu = ks
    where ks_neu isnull ;

--------------------------------------------------

Ich hoffe ich konnte das hier nachvollziehbar skizzieren.
Kann mir jemand einen Hinweis geben, wie ich das sauber in einen Loop verpacke?

 

[castorp]

Warum machst Du das nicht gleich im SELECT:

Insert into tabelle_neu (var1, var2, var3, ks)
Select f.v1, f.v2, v.v3, t1.ks
from tabelle_1  t1
   cross join x-y-funktion (t1.x, t1.y) as f

Nachdem Du uns nicht gesagt hast, wie die Spalten heissen, die von der Funktion zurückgegeben werden, habe ich einfach mal v1, v2, v3 verwendet.

 

[Hannes]

Ich wollte das mit den Spaltenbezeichnungen nicht in den Vordergrund stellen. Es geht um abgewandelte ATKIS-Daten (objart, subtype, objart_name, subtype_name, "area", percentage, code, code_name).

Ich habe meine Bedenken, dass es über Cross-Join funktioniert. Werden dabei nicht alle Kombinationsformen von x und y durchgegangen?

Für jedes ks (so werden dort bestimmte Landschaftsflächen bezeichnet) gibt es nur ein festes x,y-Paar.
Für jede Abfrage mit einem x,y-Paar kommen über die Funktion etwa 100 Zeilen mit unterschiedlichtesten Attributen zustande. Damit später die Zuordnung gelingt, muss jeder Abfrageblock um die zugehörige ks-Nummer ergänzt werden.

 

[castorp]

Ich habe meine Bedenken, dass es über Cross-Join funktioniert. Werden dabei nicht alle Kombinationsformen von x und y durchgegangen?

Damit wird für jeden Datensatz in Tabelle_1 einmal die Funktion aufgerufen. Das Resultat sind also Anzahl der Datensätze in Tabelle 1 * 100 - unterm Strich genau das Gleiche als wenn Du über die Datensätze iterierst und die Funktion aufrufst. Dann rufst Du die Funktion auch genau so oft auf wie Datensätze in der Tabelle sind. Die ks Nummer wird bei der Abfrage immer aus dem Datensatz genommen, für den die Funktion aufgerufen wurde.

Hier ist ein Beispiel (die Funktion liefert zwei Datensätze zurück mit festen Werten)

 

[Hannes]

Ok, ich probiere das mal so aus, wie Du es vorgeschlagen hast. Weniger Code als mein Ansatz ist es alle Mal. Bis hierher erstmal danke, Castorp!
Ich melde mich mit dem Ergebnis.
An dieser Stelle schon mal schönes Wochenende!

 

[Hannes]

Es scheint nicht zu funktionieren. Die Abfrage rauscht zwar ohne Fehlermeldung durch, ohne aber einen Wert zurück zu geben.
Vielleicht habe ich aber auch die Logik falsch skizziert:

In die Funktion werden die x-/y-Werte als Zentroidenwert einer Fläche (Mittelpunt) mit eine KS als Identifikationsnummer übernommen. Via Postgis werden nach bestimmten Kriterien Werte um diesen Zentroiden herum gesucht und als Tabelle ausgegeben. Die Koordinate ist ab jetzt egal. Die Zugehörigkeit der Tabellenwerte erfolgt über die KS-Nummer.

Deshalb scheint mir der Join über x/y nicht der richtige Ansatz zu sein, da keine Daten der neuen Tabelle mit den Koordinaten gematcht werden.

In meiner Kopflogik spielt sich das ganze weiterhin iterativ ab:
1) Gegeben ist eine Tabelle mit der KS-Nummer und den Koordinaten für die Zentroiden bestimmter Flächen. Außerdem eine Funktion, die die Daten innerhalb der Fläche um den jeweiligen Zentroiden ermittelt.
2) Ermittle die Daten innerhalb der Fläche um den Zentroiden
3) Speichere die Daten in einer Tabelle
4) Ergänze in der Tabelle die KS-Nummer
5) Füge die Daten an die neu Tabelle mit den anderen KS-Nummern an
6) wiederhole 2) - 5) so lange, bis alle Einträge aus der Ausgangstabelle abgearbeitet wurden.

 

[castorp]

Deshalb scheint mir der Join über x/y nicht der richtige Ansatz zu sein, da keine Daten der neuen Tabelle mit den Koordinaten gematcht werden.

Deswegen ist es ja auch ein **lateral** cross join (habe ich in meinem Beispiel nicht explizit geschrieben, weil das bei einer set returning function automatisch impliziert wird). Die Funktion wird für jede Zeile der Tabelle einmal aufgerufen und die Zeilen die die Funktion zurückgibt werden Teil des Ergebnis.

 

[Hannes]

Ok, danke für den Hinweis. Bevor ich mit meinen unwissenden Fragen fortfahre, werde ich mich nochmal vertiefen (z.B. hier).
Denn irgendwas hatte dieser Abfrage noch nicht funktioniert - auch wenn es keinen Fehler gab:

Insert into tabelle_neu (var1, var2, var3, ks)
Select f.v1, f.v2, v.v3, t1.ks
from tabelle_1  t1
   cross join x-y-funktion (t1.x, t1.y) as f

 

[castorp]

Du kannst ja mal Deine echte Funktion und die dazu gehörigen Daten mit meinem Beispiel vergleichen: Postgres 14 | db<>fiddle

 

[Hannes]

Die Funktion habe ich nicht in die von Dir vorgeschlagene Testumgebung bekommen (sie ist ziemlich komplex und verweist noch auf mehrere Tabellen der Datenbank).
Ich habe daneben weitergelesen (hier, hier oder hier) und dieses Konstrukt probiert und damit sozusagen einen Glückstreffer gelandet. Jetzt passen die Daten:

create table table_neu as
select a.ks, flaeche.*
from table_01 as a,
lateral (Select * from funktion-x-y (a.y::numeric,a.x::numeric,4326,4000)) as flaeche;