SELECT an sp übergeben und resultset zurückbekommen

Hallo liebe PG-Gemeinde,

ich bin neu in Postgres und Stored Procedures im Allgemeinen.

Ich suche eine Stored Procedure, der ich ein SELECT-Statement übergeben kann und ein Resultset zurückerhalte.

Der Grund ist, dass wir demnächst alles über Webservices machen und ich aus meinem Delphi PRojekt nicht mehr direkt auf die DB zugreifen kann.
Dort habe ich ein Modul , dass für mich die Datensteuerung übernimmt und mein SELECT ausführt. Derzeit mit direkter Datenbankverbindung.

Pseudocode:

CREATE FUNCTION sp_SELECT(statement character varying) RETURN Recordset
DECLARE
  rs Recordset;
BEGIN
  SELECT statement into rs;
  RETURN rs;
END;

Kann mir jemand sagen wie das ohne Pseudo aussieht?

Ja, da steht dann EXECUTE drin, schau in die Doku PostgreSQL: Documentation: 9.1: Basic Statements

Andreas

Danke :slight_smile:

Ich habe jetzt das hier zusammengeschwurbelt:

CREATE OR REPLACE FUNCTION sp_select(statement character varying)
   RETURNS refcursor AS
 $BODY$
 DECLARE
   ref refcursor;
 BEGIN
   EXECUTE statement into ref;
   RETURN ref;
 END;
 $BODY$
   LANGUAGE plpgsql VOLATILE
   COST 100;
 ALTER FUNCTION sp_select(character varying)
   OWNER TO postgres;

Fehler hat er schon mal keine mehr ausgegeben, allerdings kommt bei einem

SELECT * FROM sp_select('SELECT * FROM tbl_test')

als Ergebnis nur “2” raus. Also keine wirklichen Daten.

Ich möchte es nicht vorgekaut fertigprogrammiert verlangen (würde mich aber nicht wehren wenn es denn jemand machen mag :wink: ), aber ein Link wo ich etwas finde wäre gut.

Bisher habe ich nur die 2 Optionen gefunden:
Mit refcursor : dann kommt das von eben raus
Mit SetOf Object : dann kann ich die SP aber nicht mehr für beliebige tabellen verwenden oder?

Grüße

Edith hat noch mal gegooglet und noch statt

RETURN ref

das hier gefunden:

RETURN NEXT ref;
RETURN

allerdings bekomme ich dann den Fehler "RETURN NEXT kann nur in einer Funktion mit SETOF-Rückgabetyp verwendet werden.

Moment mal, du übergibst ein Query einer Stored Procedure, diese führt das dann aus und gibt das Ergebnis zurück?
Ist das nicht das gleiche als ob du das direkt ausführst?

Jain. Der Unterschied ist, dass ich einem User die Berechtigung geben kann nur eine SP auszuführen. Wenn ich diese SP später mal bearbeite kann ich alles an einer Stelle ( der SP ) ändern. SQL Injection. Rückgabe von leeren Resultsets etc.

Edit:

Das mit den Webservices vorhin war unglücklich ausgedrückt. Alles ein wenig stressig derzeit und hab Unsinn erzählt.

Die DB liegt auf einem rootserver. Die DB soll ins internet abgeschottet sein.
Das heißt in zukunft soll alles über Webservices laufen.
Ich arbeite mit Delphi.
Derzeit: Delphi greift direkt auf DB zu.
Demnächst: Delphi greift auf Webservice zu. Webservice greift auf DB zu.

Nun arbeite ich derzeit allerdings direkt mit Recordsets in Delphi. Ein Webservice kann mir aber nur rudimentäre Datentypen zurückgeben. Das höchste der Gefühle sind Arrays von Basisdatentypen.
Um meine Zugriffsstruktur aber nicht sofort ändern zu müssen, da ich dafür grade keine Zeit habe, will ich wenigstens die DB Zugriffe mit SPs verschleiern.

Daher brauche ich quasi 4 SPs. Am besten heute noch umgesetzt :slight_smile:
sp_Select(Inputstatement) : Resultset;
sp_Insert(Inputstatement) : Insert_id;
sp_Update(Inputstatement) : Void;
sp_Delete(Inputstatement) : Void;

Sollte klar sein, was die jeweils machen.

Ich finde nichts. Mir fehlt mein @@IDENTITY aus Mysql, weil Currval() mit der benannten Sequence arbeitet. also muss ich die immer mit übergeben.
Hab ich im Inputstatement nen string stehen, dann lässt sich grade nichts escapen. ich bin frustriert …

Ich lass dich mal den Bart Simpson machen: “Bitte schreibe 100 mal folgendes auf: Stored Procedures helfen nicht gegen SQL Injections!”

Schon mal die Doku gelesen?

Von hier kopiert:
PostgreSQL: Documentation: 9.1: Sequence Manipulation Functions

Schreib doch lieber
“Wenn ich diese SP später mal bearbeite kann ich alles an einer Stelle ( der SP ) ändern.” an irgendwelche Wände :slight_smile:
Ein einfacher Link wär wirklich nett. Aber ich suche mir dann wohl lieber ein englischsprachiges Forum. Beim Umschauen in den anderen Topics habe ich gesehen, was für ein Ton hier herrscht. Alle nicht ganz so einsteigerfreundlich hier.
Schade. Bis denn dann.

PS: Nicht jeder, der in euren Augen simple Fragen stellt war zu faul um Google zu bemühen. Den meisten fehlen einfach die richtigen Suchbegriffe. Aber lmgtfy mit “wirklich+nutzloser+suchbegriff” scheint hier ja Gang und Gäbe zu sein.

Edit:
Danke castorp :slight_smile:

Wenn du auf Englisch deine Anforderungen besser formulieren kannst - nur zu.
Bisher kommst du mit wichtigen Details nur spärlich um die Ecke.

Bisher kommst du mit wichtigen Details nur spärlich um die Ecke.

Danke sehr :slight_smile: Siehst du? Das war mir nicht bewusst.
Was also sollte ich euch noch mitteilen?



Das mit den Webservices vorhin war unglücklich ausgedrückt. Alles ein wenig stressig derzeit und hab Unsinn erzählt.

Nur mal so als Anfang … wir können dir nur helfen wenn du von Anfang an detailliert beschreibst was du möchtest.
Wenn du nachträglich deine Meinung änderst … nun ja.

Ich kenne Delphi nicht, aber wenn ich Deine Funktion über Java/JDBC Aufrufe dann bekomme ich folgende Fehlermeldung: cursor “2” does not exist [SQL State=34000]

Und wenn ich darüber nachdenke, dann macht das auch Sinn. In der Doku von EXECUTE steht ja, dass ein “EXECUTE … INTO” einen “Wert” zurückliefert. Um einen Cursor zu verwenden muss dieser aber geöffnet werde. Dazu ist “OPEN” notwendig: PostgreSQL: Documentation: 9.1: Cursors

Wenn man also ein OPEN statt einem EXECUTE verwendet:

CREATE OR REPLACE FUNCTION sp_select(statement character varying)
   RETURNS refcursor AS
 $BODY$
 DECLARE
   ref refcursor;
 BEGIN
   OPEN ref for execute statement;
   RETURN ref;
 END;
 $BODY$
   LANGUAGE plpgsql VOLATILE
   COST 100;

dann funktioniert das in Java/JDBC (mit einem CallableStatement - keinen Ahnung was man da in Delphi verwendet).

In psql ist das ein wenig kompliziert (siehe die Beispiele aus dem Link zum Handbuch):

postgres=> select * from sp_select('SELECT id, lastname, firstname FROM person');
     sp_select
--------------------
 <unnamed portal 3>
(1 row)


postgres=> fetch all from "<unnamed portal 3>";
 id |  lastname  | firstname
----+------------+-----------
  2 | Beeblebrox | Zaphod
  1 | Dent       | Arthur
  3 | McMillan   | Tricia
(5 rows)

postgres=>

(Kleine Bemerkung am Rande: ich habe bis jetzt nicht mit REF CURSORn in PostgreSQL gearbeit. Die obige Lösung hatte ich nachdem ich 5 Minuten das Kapitel im Handbuch zum Thema Cursor gelesen hatte. Die Infos sind alle da, wenn man sich die Mühe macht das Handbuch zu lesen.)