Selbstgeschriebene Funktionen in PostgreSQL

[Ananasinka]

Hallo zusammen,

seit Kurzem beschäftige ich mich mit SQL und versuche im Moment anhand einfacher Beispiele das Grundgerüst zu erlernen.

Ich habe eine Funktion geschrieben, die in eine vorhandene Tabelle einen Datensatz einfügen soll, falls die Geometrie (POINT) aus diesem Datensatz komplett innerhalb von der Geometrie (MULTIPOLYGON) eines Objektes aus einer anderen Tabelle liegt (also, WITHIN). Dabei sollte außerdem eine entsprechende Meldung rauskommen.

CREATE OR REPLACE FUNCTION fuegeStadtEin(stadtname text, landname text, location POINT)
    RETURNS VARCHAR AS $$
BEGIN
    IF (Within(location, bundeslaender.umriss)) THEN 
      INSERT INTO staedte VALUES (stadtname, landname, location);
      RETURN 'Die Stadt ' || stadtname || ' wurde eingetragen';
    ELSE
      RETURN 'Die Stadt ' || stadtname || ' wurde nicht eingetragen';
    END IF;
END;
$$ LANGUAGE plpgsql;

Allerdings kann ich die Methode nicht aufrufen, da nach dem Befehl

SELECT fuegeStadtEin('Hannover', 'Niedersachsen', PointFromText('POINT(12 9)', -1));

immer wieder eine Fehlermeldung kommt:

ERROR: function fuegestadtein(unknown, unknown, geometry) does not exist
LINE 1: SELECT fuegeStadtEin('Hannover', 'Niedersachsen', PointFromT...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Ich verstehe nicht, warum der Parametertyp plötzlich "unknown" wird. Woran könnte es liegen?

Vielen Dank für eure Hilfe.

 

[mermshaus]

Ich tippe, ein String-Parameter wird von der DB je nach Typ des Zielfelds ausgewertet, könnte also auch als INT durchgehen o. ä. und ist deshalb an der Stelle vom Typ "unknown".

Wenn man der Fehlermeldung glauben darf, könnten explizite Typecasting-Funktionen helfen.

Nach dem Schema:

SELECT fuegeStadtEin(TYPECASTFUNKTION('Hannover'), 
TYPECASTFUNKTION('Niedersachsen'), PointFromText('POINT(12 9)', -1))

PostgreSQL: Documentation: Manuals: PostgreSQL 7.3: Value Expressions

(Keine Ahnung, ob der Link passt. Ich kenne mich damit nicht wirklich aus.)

Edit: Hm, so richtig plausibel kommt mir das doch nicht vor...

Schaffst du es, die Funktion überhaupt irgendwie aufzurufen?

 

[Ananasinka]

Ich habe gerade deinen Vorshlag ausprobiert - die Fehlermeldung sieht jetzt wie folgend aus:

ERROR: function typecastfunktion(unknown) does not exist
LINE 94: SELECT fuegeStadtEin(TYPECASTFUNKTION('Hannover'),
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Ich glaube nicht, dass es an den unbekannten Datentyp liegt, sondern vermute, dass SELECT an meine Funktion einfach nicht zugreifen kann.

 

[mermshaus]

Ja, siehe mein Edit (gutes Timing ;)). Das TYPECASTFUNKTION war übrigens nur ein Platzhalter, um anzudeuten, wo du die richtige Typecastfunktion einsetzen solltest. Ich weiß nicht, wie die entsprechende Syntax in PostgreSQL aussieht.

 

[Ananasinka]

Schaffst du es, die Funktion überhaupt irgendwie aufzurufen?

Nein, ich hab schon mit verschiedenen Varianten ausprobiert. Das Programm kommt genau an der oben genannten Stelle mit dem SELECT nicht klar, egal, was in der Funktion steht.

gutes Timing

Ich weiß :)
Wenn man schon seit zwei Tagen an einer Stelle sitzt und nichts findet, passt man sehr gut auf jeden nützlichen Tipp auf

 

[mermshaus]

Scheint doch am Typecasting zu liegen.

SELECT fuegeStadtEin(TEXT('Hannover'),
                     TEXT('Niedersachsen'),
                     PointFromText('POINT(12 9)', -1));

Fehlermeldung:

SQL error:

ERROR:  function pointfromtext(unknown, integer) does not exist
LINE 1: ...StadtEin(TEXT('Hannover'), TEXT('Niedersachsen'), PointFromT...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

In statement:

SELECT fuegeStadtEin(TEXT('Hannover'), TEXT('Niedersachsen'), PointFromText('POINT(12 9)', -1));

 

[Ananasinka]

Danke, ich hab auch den letzten Parameter mit POINT(..) gekastet und jetzt ist ein anderer Fehler gekommen :)

ERROR: missing FROM-clause entry for table "bundeslaender"
LINE 1: SELECT (Within( $1 , bundeslaender.umriss))
^
QUERY: SELECT (Within( $1 , bundeslaender.umriss))
CONTEXT: PL/pgSQL function "fuegestadtein" line 2 at IF

Das Programm verlangt nach der Anweisung FROM, aber wenn ich die hinzufüge, kommt trotzdem eine Fehlermeldung, da es offenbar nicht zulässig ist, so was zu schreiben.

ERROR: syntax error at or near "FROM"
LINE 1: SELECT (Within( $1 , bundeslaender.umriss) FROM bundeslaend...
^
QUERY: SELECT (Within( $1 , bundeslaender.umriss) FROM bundeslaender)
CONTEXT: SQL statement in PL/PgSQL function "fuegestadtein" near line 2

PS. Ich glaube trotzdem immer noch nicht, dass an den Fehler Casting schuldig ist. Ich hab mehrere Beipiele gelesen, wo neue Datensätze ohne Casting in die Tabellen hinzugefügt werden können.
Jetzt nach der letzten Fehlermeldung kommt es mir so vor, als das Problem nämlich an der Funktion liegt. Die ist irgendwie nicht fehlerfrei, aber wo genau..

 

[mermshaus]

Die Tabellen existieren alle? Weißt du, was du da tust und ob es sinnvoll ist, das so zu tun? Wo ist die Within-Funktion definiert? Wie wäre es mit etwas mehr Kontext?

 

[Ananasinka]

Ja, natürlich exsistieren sie.
Die Tabelle "bundeslaender"
- hat 4 Datensätze und
- folgende Felder: bundeland text (als primary key), umriss (beschreibt die geometrie von bundesländern, vom typ MULTIPOLYGON)

Die Tabelle "staedte":
- im Moment leer (muss nämlich anhand der Funktion mit den Werten gefüllt werden)
- Felder: stadtname text (als primary key),
landname text (als foreign key für den Feld bundesland von der Tabelle "bundeslaender")
location (beschreibt die Geometrie, vom typ POINT)
- hat als REFERENCE Tabelle "bundeslaender"

Within-Funktion ist eine in PostGIS eingebaute Funktion, außerhalb von meiner Funktion fuegeStadtEin funktioniert sie ganz gut und liefert einen boolischen Wert zurück, also richtig oder falsch, je nachdem, ob eine Geometrie in einer anderen enthalten ist. Das habe ich mehrmals ausprobiert, funktioniert unproblematisch.

Muss man vielleicht irgendwie zusätzlich definieren, wie man an eine Funktion zugreifen kann? Es scheint so zu sein, dass sie irgendwie zu "privat" ist

 

[mermshaus]

Ich kann das leider nicht testen. PostgreSQL habe ich gestern noch aufgesetzt, aber es ist mir zu anstrengend, herauszufinden, wie das mit PostGIS geht.

Ziemlich sicher kannst du aber die IF-Abfrage einfach nicht so formulieren (deshalb die Nachfrage im letzten Post). Im Beispiel in der Dokumentation (GIS | PostGIS Tutorial - GISWiki) heißt es etwa:

SELECT gr.eigentuemer, b.name
 FROM grundstuecke_nor AS gr, baeume_nor AS b
 WHERE Within ( b.geom, gr.geom ) = TRUE;

Was möchtest du denn zurückerhalten? Das Bundesland, in dem location liegt? Oder lediglich die Information, ob location in einem Bundesland liegt?

Letzteres dürfte ungefähr so gehen:

IF ((SELECT COUNT(*)
 FROM bundeslaender
 WHERE Within ( location, umriss ) = TRUE) > 0) THEN

Das ist allerdings nur MySQL-Pseudosyntax. Ich kann nicht sagen, ob das unter PostgreSQL ohne Anpassungen funktioniert.

 

[Ananasinka]

Im Beispiel in der Dokumentation (GIS | PostGIS Tutorial - GISWiki) heißt es etwa:

SELECT gr.eigentuemer, b.name
 FROM grundstuecke_nor AS gr, baeume_nor AS b
 WHERE Within ( b.geom, gr.geom ) = TRUE;

Diese Dokumentation hat einer der Professoren von meiner Uni geschrieben :)

Das liegt nicht an TRUE, das ist ganz sicher. Es ist bei PostGIS so default definiert, dass die Bedingungen

Within ( b.geom, gr.geom )

und

Within ( b.geom, gr.geom ) = TRUE

gleich sind. Also auch wenn es kein "= TRUE" steht, bedeutet das trotzdem, dass irgendwas gemacht wird, nur falls die Bedingung erfüllt wird.

Was möchtest du denn zurückerhalten? Das Bundesland, in dem location liegt? Oder lediglich die Information, ob location in einem Bundesland liegt?

Falls die angegebene Stadt (Geometrytyp POINT) innerhalb vom Bundesland (Geometrytyp MULTIPOLYGON) liegt, soll:
1. diese Stadt in die Tabelle "staedte" eingefügt werden (Benutzer sieht das Ergebnis nicht, das muss einfach geschehen)
2. Für den Benutzer soll eine Meldung kommen, dass eine Stadt eingefügt bzw. nicht eingefügt (falls Stadt außerhalb liegt) wurde - also ich benotige nur die Info, ob location in einem Bundesland liegt.

Das Bundesland, in dem die Stadt liegen soll, bestimme ich schon bei dem Aufruf von der Methode (hier - Niedersachsen):

SELECT fuegeStadtEin(TEXT('Hannover'), TEXT('Niedersachsen'), PointFromText('POINT(12 9)', -1));

Da die Tabellen "bundeslaender" und "staedte" durch den Fremdschlüssel "bundesland" (nämlich der Name vom Bundesland) verknüpft sind, sollte meiner Meinung nach das ziemlich eindeutig adressiert werden.

Ich glaube, dass meine Funktion gleich nach der Beschaffung einfach stirbt - genau deswegen kommt die Meldung:

LINE 96: SELECT fuegeStadtEin('Hannover', 'Niedersachsen', PointFromT...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Das Programm findet sie einfach nicht, obwohl die Beschaffung (durch CREATE FUNCTION) fehlerfrei läuft.
Aber wie ich die "am Leben" halten kann, weiß ich leider nicht.

 

[mermshaus]

Ich glaube, dass meine Funktion gleich nach der Beschaffung einfach stirbt - genau deswegen kommt die Meldung:

Das stimmt nicht. Wir kommen einen Schritt voran, wenn wir explizit typecasten (siehe die neue Fehlermeldung in #6). Ergo: Es liegt an der Stelle am Typecasting. Die Funktion selbst wird gefunden und aufgerufen.

Das liegt nicht an TRUE, das ist ganz sicher. Es ist bei PostGIS so default definiert, dass die Bedingungen

Das habe ich auch nie behauptet... Mir ging es nicht um das TRUE, sondern um den Unterschied zwischen:

    IF (Within(location, bundeslaender.umriss)) THEN

und

IF ((SELECT COUNT(*)
 FROM bundeslaender
 WHERE Within ( location, umriss ) = TRUE) > 0) THEN

. Also um das SELECT. Deine Variante ist syntaktisch ziemlich sicher falsch.

Deshalb fragte ich in #8, ob du Ahnung von dem hast, was du da tust. Ich bin mir nämlich nicht absolut sicher, dass das nicht in dem DBMS bzw. der Language vielleicht doch so geht.

Edit: Ich kenne deinen Anwendungsfall nicht, aber warum löst du das überhaupt über eine DB-interne Funktion?