PostgreSQL параметризован Order By/Limit в функции таблицы

Ответ 1

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

• Разница между языком sql и языком plpgsql в функциях PostgreSQL

В этом случае это намного проще, чем много предложений CASE в запросе:

CREATE OR REPLACE FUNCTION get_stuff(_param text, _orderby text, _limit int)
  RETURNS SETOF stuff AS
$BODY$
BEGIN

RETURN QUERY EXECUTE '
    SELECT *
    FROM   stuff
    WHERE  col = $1
    ORDER  BY ' || quote_ident(_orderby) || '
    LIMIT  $2'
USING _param, _limit;

END;
$BODY$
  LANGUAGE plpgsql;

Вызов:

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

Примечания

• Используйте RETURN QUERY EXECUTE, чтобы вернуть результаты запроса за один раз.
• Используйте quote_ident() для идентификаторов для защиты от SQLi.
• Используйте USING, чтобы передать значения параметров, чтобы избежать кастинга, цитирования и SQLi еще раз.
• Будьте осторожны, чтобы не создавать конфликты имен между именами параметров и столбцов. Я префиксные имена параметров с "_" в примере.

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

Ответ 2

Вы можете передать предельное значение как аргумент функции без каких-либо проблем. Что касается заказа, вы можете использовать ODER BY в сочетании с оператором CASE. Это, к сожалению, не будет работать для чего-то вроде

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

Ответ 3

Что касается 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 совпадали. Просто будьте осторожны с преобразованием чисел в строки - вам нужно будет добавить 0s, чтобы сделать их правильно. То же самое относится к значениям даты/времени. Заказывайте по формату, в котором есть год, за которым следует месяц, а затем день и т.д.

Я сделал это на SQL Server, но никогда не был в PostgreSQL, и у меня нет копии PostgreSQL на этой машине, поэтому это не проверено.

Ответ 4

Если ваша функция stable (не изменяет базу данных), планировщик запросов будет обычно inline. Поэтому выполнение 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.