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

Нет ничего плохого в функции 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;