Optionales argument, das in PL/pgSQL-Funktion

Ich bin versucht zu schreiben Sie eine PL/pgSQL-Funktion mit optionalen Argumenten. Es führt eine Abfrage basierend auf einer gefilterten Gruppe von Datensätzen (falls angegeben), sonst führt eine Abfrage auf die gesamten Daten in einer Tabelle.

Beispielsweise (PSEUDO-CODE):

CREATE OR REPLACE FUNCTION foofunc(param1 integer, param2 date, param2 date, optional_list_of_ids=[]) RETURNS SETOF RECORD AS $$
    IF len(optional_list_of_ids) > 0 THEN
        RETURN QUERY (SELECT * from foobar where f1=param1 AND f2=param2 AND id in optional_list_of_ids);
    ELSE
        RETURN QUERY (SELECT * from foobar where f1=param1 AND f2=param2);
    ENDIF
$$ LANGUAGE SQL;

Was wäre der richtige Weg, um diese Aufgabe umzusetzen?

Als ein beiseite, ich würde gerne wissen, wie ich könnte so eine Funktion in einer anderen äußeren Funktion. Dies ist, wie ich es tun würde - ist es richtig, oder gibt es eine bessere Möglichkeit?

CREATE FUNCTION foofuncwrapper(param1 integer, param2 date, param2 date) RETURNS SETOF RECORD AS $$
BEGIN
   CREATE TABLE ids AS SELECT id from foobar where id < 100;
   RETURN QUERY (SELECT * FROM foofunc(param1, param2, ids));
END
$$ LANGUAGE SQL
InformationsquelleAutor Homunculus Reticulli | 2012-07-17
  1. 42

    Seit PostgreSQL 8.4 (was Sie zu sein scheinen, laufen), es gibt default-Werte für Funktions-Parameter. Wenn Sie die parameter der letzten und der eine Standardeinstellung, können Sie einfach weglassen, es aus dem Aufruf:

    CREATE OR REPLACE FUNCTION foofunc(_param1 integer
                                 , _param2 date
                                 , _ids    int[] DEFAULT '{}')
  RETURNS SETOF foobar AS -- declare return type!
$func$
BEGIN  -- required for plpgsql
   IF _ids <> '{}'::int[] THEN -- exclude empty array and NULL
      RETURN QUERY
      SELECT *
      FROM   foobar
      WHERE  f1 = _param1
      AND    f2 = _param2
      AND    id = ANY(_ids); -- "IN" is not proper syntax for arrays
   ELSE
      RETURN QUERY
      SELECT *
      FROM   foobar
      WHERE  f1 = _param1
      AND    f2 = _param2;
   END IF;
END  -- required for plpgsql
$func$  LANGUAGE plpgsql;

    Wichtigsten Punkte:

    • Dem Stichwort DEFAULT wird verwendet, um zu deklarieren parameter ist der Standardwert. Kurze alternative: =.

    • Habe ich entfernt redundante param1 von der unordentlichen Beispiel.

    • Seit Sie zurück SELECT * FROM foobar deklarieren des Rückgabetyps als RETURNS SETOF foobar statt RETURNS SETOF record. Die letztere form mit anonymisierter Datensätze ist sehr unhandlich, Sie müssten, um eine definition für die Spalte Liste bei jedem Aufruf.

    • Benutze ich ein array of integer (int[]) als Funktion der parameter. Angepasst IF Ausdruck und die WHERE - Klausel entsprechend.

    • IF - Anweisungen sind nicht verfügbar in plain SQL. Hat LANGUAGE plpgsql für, die.

    Anruf mit oder ohne _ids:

    SELECT * FROM foofunc(1, '2012-1-1'::date);

    Effektiv das gleiche:

    SELECT * FROM foofunc(1, '2012-1-1'::date, '{}'::int[]);

    Müssen Sie sicherstellen, dass der Aufruf ist eindeutig. Wenn Sie eine andere Funktion mit dem gleichen Namen und mit zwei Parametern, Postgres kann nicht wissen, welche zu wählen. Die explizite Umwandlung (wie ich zeige) verengt es nach unten. Andere, nicht typisierten string-Literale, arbeiten zu müssen, aber als explizite schadet nie.

    Aufruf innerhalb einer anderen Funktion:

    CREATE FUNCTION foofuncwrapper(_param1 integer, _param2 date)
  RETURNS SETOF foobar AS
$func$
DECLARE
   _ids int[] := '{1,2,3}';
BEGIN
   -- irrelevant stuff

   RETURN QUERY
   SELECT * FROM foofunc(_param1, _param2, _ids);
END
$func$  LANGUAGE plgpsql;
    • +1 für die Beantwortung der Frage. Das ist genau das was ich gesucht habe!
    InformationsquelleAutor Erwin Brandstetter
  2. 3

    Erarbeiten Frank's Antwort auf diesen thread:

    Den VARIADIC agument nicht das einzige argument, nur der Letzte.

    Können Sie VARIADIC für Funktionen, die möglicherweise nehmen null Variable Argumente, es ist nur ein wenig fiddlier, erfordert eine andere aufrufen Stil für null, args. Sie können eine wrapper-Funktion zum ausblenden der Hässlichkeit. Aufgrund einer ersten varardic definition einer Funktion wie:

    CREATE OR REPLACE FUNCTION foofunc(param1 integer, param2 date, param2 date, optional_list_of_ids VARIADIC integer[]) RETURNS SETOF RECORD AS $$
....
$$ language sql;

    Null args benutzen einen wrapper wie:

    CREATE OR REPLACE FUNCTION foofunc(integer, date, date) RETURNS SETOF RECORD AS $body$
SELECT foofunc($1,$2,$3,VARIADIC ARRAY[]::integer[]);
$body$ LANGUAGE 'sql';

    oder rufen Sie die main-Funktion mit einem leeren array wie VARIADIC '{}'::integer[] direkt. Die Hülle ist hässlich, aber es ist enthalten Hässlichkeit, so würde ich empfehlen einen wrapper.

    Direkte Anrufe können gemacht werden, Variable form:

    SELECT foofunc(1,'2011-01-01','2011-01-01', 1, 2, 3, 4);

    ... oder array-call-Formular mit array-ctor:

    SELECT foofunc(1,'2011-01-01','2011-01-01', VARIADIC ARRAY[1,2,3,4]);

    ... oder array-literalen text form:

    SELECT foofunc(1,'2011-01-01','2011-01-01', VARIADIC '{1,2,3,4}'::int[]);

    Letztere zwei Formen funktionieren mit leeren arrays.

    • Ich glaube, ich kann ändern Sie einfach die Funktion der Signatur zu CREATE OR REPLACE FUNCTION foofunc(int, date, date, VARIADIC ARRAY[]::integer[]) ? und überlast die Funktion mit einem wrapper für eine leere Liste (ich denke, das ist, was Sie sagen). Nun, in Bezug auf die übergabe einer Liste von ids, wie kann ich das übergeben eine Liste von ids zu foofunc() - bitte beachten Sie die beiseite unten meine Frage.
    • Frage aktualisiert, weitere Evidenz und Klarheit. Hoffe, es hilft. Ich habe nicht dummied mit syntax überprüft, ich arbeite aus dem Gedächtnis, aber ich habe ein bisschen mit variadischen Funktionen im Pg vor kurzem so sollte es richtig sein.
    • Der Letzte Teil des vorgeschlagenen Unterschrift ungültig; Sie können nicht nehmen Sie einen array-Konstruktor, der als argument-Typ. Weglassen der ARRAY[]::, einfach akzeptieren variable_name VARIADIC integer[] .
    InformationsquelleAutor Craig Ringer
  3. 1

    Du meinst SQL-Funktionen mit Variabler Anzahl von Argumenten? Wenn dem so ist, verwenden Sie VARIADIC.

    • Beachten Sie, dass VARIADIC verhält sich anders, was Sie möglicherweise verwendet werden, um mit varargs in einigen Sprachen. Insbesondere die null-Variable Argumente werden nicht akzeptiert. Wenn Sie möchten, um den Anruf mit null Variable Argumente, verwenden Sie die syntax the_varargs_function(fixedarg1, fixedarg2, VARIADIC ARRAY[]::argtype[]) wo argtype[] ist der Typ der Variable array-argument. Es ist oft hilfreich sein, schreiben einen einfachen LANGUAGE SQL wrapper-Funktion, die einen null-argument-form.
    • Nicht sicher, ob VARIABLE ist nützlich in allen Szenarien für mich. Nur das Letzte argument, das kann (oder können), bestehen aus einer Liste von ids. Die vorangegangenen Argumente param1, param2 etc wird immer vorhanden sein.
    • Ja, das klingt mehr nach dem Weg, den ich gehen möchte. Könnten Sie bitte konkretisieren Sie Ihren Kommentar als eine Antwort (am besten mit ein wenig SQL-code), so dass ich daraus lernen?. Dank
    InformationsquelleAutor Frank Heikens
Schreibe einen Kommentar