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

Question

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

У меня есть функция sql, которая делает простой оператор выбора SQL:

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

Сейчас я вызываю эту функцию следующим образом:

select * from getStuff('hello');

Каковы мои варианты, если мне нужно заказать и ограничить результаты с order by а также limit пункты?

Я думаю, такой запрос:

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

не будет очень эффективным, потому что все строки из таблицы stuff будет возвращено функцией getStuff и только потом заказал и нарезал лимитом.

Но даже если я прав, нет простого способа передать порядок по аргументу функции языка SQL. Могут быть переданы только значения, а не части оператора SQL.

Другой вариант - создать функцию в plpgsql язык, на котором можно построить запрос и выполнить его через EXECUTE, Но это тоже не очень хороший подход.

Итак, есть ли другой способ достижения этого? Или какой вариант вы бы выбрали? Порядок / ограничение вне функции или plpgsql?

Я использую postgresql 9.1.

редактировать

Я изменил оператор CREATE FUNCTION следующим образом:

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;

~~Это бросает:~~

ОШИБКА: CASE вводит разные символы и не может быть сопоставлено целое число ŘÁDKA 13: WHEN $1 = 'parent' THEN t.parent

stuff таблица выглядит так:

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),
    )

Edit2

Я плохо прочитал код Dems. Я исправил это к вопросу. Этот код работает для меня.

22

sql database postgresql plpgsql dynamic-sql

Источник

user821113 15 ноя '11 в 16:25

5 ответов

Решение

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

5

Источник

user932715 27 окт '15 в 13:25

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

Я сделал это в SQL Server, но никогда в PostgreSQL, и у меня нет копии PostgreSQL на этом компьютере, так что это не проверено.

2

Источник

user5696608 15 ноя '11 в 16:42

Использование функции форматирования Даже при ilikeприкольный оператор.

      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ссылка: https://www.postgresql.org/docs/current/functions-string.html

1

Источник

user15603477 08 дек '21 в 08:50

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

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

0

Источник

user642340 15 ноя '11 в 16:41

Другие вопросы по тегам sql database postgresql plpgsql dynamic-sql

user939860 16 ноя '11 в 03:17 2011-11-16 03:17 · Accepted Answer · 2011-11-16 03:17

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