PostgreSQL параметризира Поръчка по/Ограничение в таблична функция

Няма нищо лошо в функция plpgsql за нещо малко по-сложно. Единствената ситуация, при която производителността може да се влоши, е когато е вложена функция plpgsql, тъй като програмата за планиране на заявки не може допълнително да оптимизира съдържащия се код в контекста на външната заявка, което може или не може да я направи по-бавна.
Повече подробности в това по-късно отговор:

• Разлика между език sql и език plpgsql в Функции на PostgreSQL

В настоящия случай е много по-просто от много CASE клаузи в заявка:

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;

Обадете се:

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

Бележки

Използвайте RETURN QUERY EXECUTE, за да върнете резултатите на заявка наведнъж.

Използвайте quote_ident() за идентификатори за защита срещу SQLi.
Или format() за нещо по-сложно. Вижте:

• Име на таблица като параметър на функция на PostgreSQL

Предавайте стойностите на параметрите с USING клауза, за да се избегне отново кастинг, цитиране и SQLi.

Внимавайте да не създавате конфликти при именуване между параметри и имена на колони. В примера поставих имената на параметрите с долна черта (_). Просто мое лично предпочитание.

Втората ви функция след редакцията не може да работи, защото връщате само parent, докато типът на връщане е деклариран SETOF stuff. Можете да декларирате всеки тип връщане, който искате, но действителните връщани стойности трябва да съвпадат с декларацията. Може да искате да използвате RETURNS TABLE за това.

Ако функцията ви е стабилна (не променя базата данни), програмата за планиране на заявки обикновено ще го вгради. Следователно извършването на SELECT * FROM getStuff('x') LIMIT 10 ще създаде същия план на заявка, както ако лимитът е вътре в getStuff().

Трябва обаче да кажете на PG, че вашата функция е стабилна, като я декларирате като такава:

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

Сега извършването на EXPLAIN SELECT * FROM getStuff('x') LIMIT 1 би трябвало да произведе същия план на заявка, както би написала еквивалентната заявка.

Вграждането трябва да работи и за ORDER BY клаузи извън функцията. Но ако искате да параметризирате функцията, за да определите реда, можете да го направите по следния начин, за да контролирате и посоката на сортиране:

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
$$;

Докато sort_col и sort_dir са постоянни в рамките на заявката, програмата за планиране на заявки трябва да може да опрости многословно изглеждащата заявка до

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

което можете да проверите с помощта на EXPLAIN.

Що се отнася до ORDER BY, можете да опитате нещо подобно:

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

Може да се наложи да направите някои преобразувания на типове данни, така че всички типове данни в резултата CASE да съвпадат. Просто внимавайте с преобразуването на числата в низове - ще трябва да добавите 0 преди, за да ги подредите правилно. Същото важи и за стойностите за дата/час. Подредете по формат, който има година, последвана от месец, последван от ден и т.н.

Правил съм това в SQL Server, но никога в PostgreSQL и нямам копие на PostgreSQL на тази машина, така че това не е тествано.

Можете да подадете гранична стойност като аргумент на функция без никакви проблеми. Що се отнася до подреждането, можете да използвате ODER BY в комбинация с оператор CASE. Това за съжаление няма да работи за нещо подобно

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