PostgreSQL parametrisierte Order By / Limit in Tabellenfunktion

Question

Ich habe eine Sql-Funktion, die eine einfache Sql Select-Anweisung tut:

CREATE OR REPLACE FUNCTION getStuff(param character varying)
  RETURNS SETOF stuff AS
$BODY$
    select *
    from stuff
    where col = $1
$BODY$
  LANGUAGE sql;

Im Moment rufe ich diese Funktion wie folgt auf:

select * from getStuff('hello');

Welche Möglichkeiten habe ich, wenn ich eine Bestellung aufgeben und die Ergebnisse einschränken muss mit order by y limit Klauseln?

Ich denke, eine Anfrage wie diese:

select * from getStuff('hello') order by col2 limit 100;

wäre nicht sehr effizient, da alle Zeilen der Tabelle stuff wird zurückgegeben von der Funktion getStuff und erst dann geordnet und in Scheiben geschnitten werden.

Aber selbst wenn ich Recht habe, gibt es keinen einfachen Weg, wie man die Reihenfolge als Argument einer Sql-Sprachfunktion übergeben kann. Es können nur Werte übergeben werden, keine Teile einer SQL-Anweisung.

Eine andere Möglichkeit ist die Erstellung der Funktion in plpgsql Sprache, in der es möglich ist, die Abfrage zu konstruieren und sie über EXECUTE . Aber auch das ist kein besonders schöner Ansatz.

Gibt es eine andere Methode, dies zu erreichen? Oder welche Option würden Sie wählen? Anordnung/Begrenzung außerhalb der Funktion oder plpgsql?

Ich verwende Postgresql 9.1.

Editar

Ich habe die CREATE FUNCTION-Anweisung wie folgt geändert:

CREATE OR REPLACE FUNCTION getStuff(param character varying, orderby character varying)
  RETURNS SETOF stuff AS
$BODY$
    select t.*
    from stuff t
    where col = $1
    ORDER BY
        CASE WHEN $2 = 'parent' THEN t.parent END,
        CASE WHEN $2 = 'type' THEN t."type" END, 
        CASE WHEN $2 = 'title' THEN t.title END

$BODY$
  LANGUAGE sql;

Das wirft:

ERROR: CASE-Typen Zeichen variierend und Ganzzahl können nicht übereinstimmen RÁDKA 13: WENN $1

En stuff Tabelle sieht wie folgt aus:

CREATE TABLE stuff
    (
      id integer serial,
      "type" integer NOT NULL,
      parent integer,
      title character varying(100) NOT NULL,
      description text,
      CONSTRAINT "pkId" PRIMARY KEY (id),
    )

Bearbeiten2

Ich habe Dems Code schlecht gelesen. Ich habe ihn auf Frage korrigiert. Dieser Code ist für mich arbeiten.

Answer 1

Es ist nichts falsch an einer plpgsql-Funktion für alles, was ein wenig komplexer ist. Die einzige Situation, in der die Leistung leiden kann, ist, wenn eine plpgsql-Funktion verschachtelt ist, weil der Abfrageplaner den enthaltenen Code im Kontext der äußeren Abfrage nicht weiter optimieren kann, was ihn langsamer machen kann oder auch nicht.
Weitere Einzelheiten in dieser späteren Antwort:

• Unterschied zwischen der Sprache sql und der Sprache plpgsql in PostgreSQL-Funktionen

Das ist viel einfacher als viele CASE Klauseln in einer Abfrage:

CREATE OR REPLACE FUNCTION get_stuff(_param text, _orderby text, _limit int)
  RETURNS SETOF stuff AS
$func$
BEGIN
   RETURN QUERY EXECUTE '
      SELECT *
      FROM   stuff
      WHERE  col = $1
      ORDER  BY ' || quote_ident(_orderby) || ' ASC
      LIMIT  $2'
   USING _param, _limit;
END
$func$  LANGUAGE plpgsql;

Anrufen:

SELECT * FROM get_stuff('hello', 'col2', 100);

Anmerkungen

Verwenden Sie RETURN QUERY EXECUTE um die Ergebnisse der Abfrage in einem Rutsch zu erhalten.

Verwenden Sie quote_ident() für Identifikatoren zum Schutz vor SQLi.
Oder format() für alles, was komplexer ist. Siehe:

• Tabellenname als Parameter einer PostgreSQL-Funktion

Übergeben Sie Parameterwerte mit der USING Klausel, um wieder einmal Casting, Quoting und SQLi zu vermeiden.

Achten Sie darauf, dass keine Namenskonflikte zwischen Parametern und Spaltennamen entstehen. Ich habe den Parameternamen einen Unterstrich vorangestellt ( _ ) im Beispiel. Nur meine persönliche Vorliebe.

Ihre zweite Funktion nach dem Editieren kann nicht funktionieren, weil Sie nur parent während der Rückgabetyp deklariert wird SETOF stuff . Sie können erklären jede Rückgabetyp nach Belieben, aber die tatsächlichen Rückgabewerte müssen mit der Deklaration übereinstimmen. Vielleicht möchten Sie RETURNS TABLE dafür.

Answer 2

Wenn Ihre Funktion stabil (ändert die Datenbank nicht), wird der Abfrageplaner normalerweise Inline es. Daher ist das Tun SELECT * FROM getStuff('x') LIMIT 10 wird denselben Abfrageplan erzeugen, als ob die Grenze innerhalb von getStuff() .

Allerdings müssen Sie PG mitteilen, dass Ihre Funktion stabil ist, indem Sie sie als solche deklarieren:

CREATE OR REPLACE FUNCTION getStuff(param varchar)
RETURNS setof STUFF
LANGUAGE SQL
STABLE
AS $$ ... $$;

Jetzt tun EXPLAIN SELECT * FROM getStuff('x') LIMIT 1 sollte den gleichen Abfrageplan ergeben, wie wenn man die entsprechende Abfrage ausschreiben würde.

Das Inlining sollte auch funktionieren für ORDER BY Klauseln außerhalb der Funktion. Aber wenn Sie die Funktion parametrisieren wollten, um die Reihenfolge zu bestimmen, könnten Sie es so machen, um auch die Sortierrichtung zu steuern:

CREATE FUNCTION sort_stuff(sort_col TEXT, sort_dir TEXT DEFAULT 'asc')
RETURNS SETOF stuff
LANGUAGE SQL
STABLE
AS $$
    SELECT *
    FROM stuff
    ORDER BY
      -- Simplified to NULL if not sorting in ascending order.
      CASE WHEN sort_dir = 'asc' THEN
          CASE sort_col
              -- Check for each possible value of sort_col.
              WHEN 'col1' THEN col1
              WHEN 'col2' THEN col2
              WHEN 'col3' THEN col3
              --- etc.
              ELSE NULL
          END
      ELSE
          NULL
      END
      ASC,

      -- Same as before, but for sort_dir = 'desc'
      CASE WHEN sort_dir = 'desc' THEN
          CASE sort_col
              WHEN 'col1' THEN col1
              WHEN 'col2' THEN col2
              WHEN 'col3' THEN col3
              ELSE NULL
          END
      ELSE
          NULL
      END
      DESC
$$;

Solange sort_col y sort_dir innerhalb der Abfrage konstant sind, sollte der Abfrageplaner in der Lage sein, die langatmig aussehende Abfrage zu vereinfachen

SELECT *
FROM stuff
ORDER BY <sort_col> <sort_dir>

was Sie überprüfen können mit EXPLAIN .

Answer 3

In Bezug auf die ORDER BY könnten Sie so etwas versuchen:

SELECT
    <column list>
FROM
    Stuff
WHERE
    col1 = $1
ORDER BY
    CASE $2
        WHEN 'col1' THEN col1
        WHEN 'col2' THEN col2
        WHEN 'col3' THEN col3
        ELSE col1  -- Or whatever your default should be
    END

Möglicherweise müssen Sie einige Datentypkonvertierungen vornehmen, damit alle Datentypen in der Datei CASE Ergebnis übereinstimmen. Seien Sie nur vorsichtig bei der Umwandlung von Zahlen in Zeichenketten - Sie müssen 0en vorangestellt werden, damit die Reihenfolge stimmt. Das Gleiche gilt für Datums-/Zeitwerte. Ordnen Sie nach einem Format, das Jahr gefolgt von Monat gefolgt von Tag usw. enthält.

Ich habe das schon mit SQL Server gemacht, aber noch nie mit PostgreSQL, und ich habe keine Kopie von PostgreSQL auf diesem Rechner, also ist das ungetestet.

Answer 4

Verwendung der Funktion Format Auch mit ilike ausgefallener Betreiber.

CREATE OR REPLACE FUNCTION get_customer(
      _param text, _orderby text, _limit int)
      RETURNS SETOF customer AS
$func$

BEGIN
   RETURN QUERY EXECUTE format('
      SELECT *
      FROM   customer
      WHERE  first_name ilike ''%%%s%%''
      ORDER  BY  %I DESC
      LIMIT  %L',
      _param, _orderby, _limit );
END
$func$  LANGUAGE plpgsql;

Format Hinweis: https://www.postgresql.org/docs/current/functions-string.html

Answer 5

Sie können den Grenzwert ohne Probleme als Funktionsargument übergeben. Für die Reihenfolge können Sie ODER BY in Kombination mit der CASE-Anweisung verwenden. Dies funktioniert leider nicht für etwas wie

ORDER BY CASE condition_variable
WHEN 'asc' THEN column_name ASC
ELSE column_name DESC
END;