Подтвердить что ты не робот

Есть ли способ определить именованную константу в запросе PostgreSQL?

Есть ли способ определить именованную константу в запросе PostgreSQL? Например:

MY_ID = 5;
SELECT * FROM users WHERE id = MY_ID;
4b9b3361

ОТВЕТЫ

Ответ 1

Этот вопрос задан раньше (Как вы используете переменные script в PostgreSQL?). Тем не менее, есть трюк, который иногда используется для запросов:

with const as (
    select 1 as val
)
select . . .
from const cross join
     <more tables>

То есть, я определяю CTE, называемый const, который имеет определенные там константы. Затем я могу пересечь это в свой запрос, любое количество раз на любом уровне. Я нашел это особенно полезным, когда я занимаюсь датами, и мне нужно обрабатывать константы даты во многих подзапросах.

Ответ 2

PostgreSQL не имеет встроенного способа определения (глобальных) переменных, таких как MySQL или Oracle. (Существует ограниченное обходное решение, использующее "настраиваемые параметры"). В зависимости от того, что вы хотите, есть другие способы:

Для одного запроса

Вы можете предоставить значения в верхней части запроса в CTE, например, уже предоставленном GGordon.

Глобальная постоянная постоянная:

Вы можете создать для IMMUTABLE функцию IMMUTABLE:

CREATE FUNCTION public.f_myid()
  RETURNS int IMMUTABLE LANGUAGE SQL AS
'SELECT 5';

Он должен жить в схеме, которая видна текущему пользователю, то есть находится в соответствующем пути search_path. Как и public схема, по умолчанию. Если проблема search_path безопасностью, убедитесь, что она первая схема в пути search_path или schema - квалифицирует ее в вашем вызове:

SELECT public.f_myid();

Видимый для всех пользователей в базе данных (которым разрешен доступ к public схеме).

Несколько значений для текущего сеанса:

CREATE TEMP TABLE val (val_id int PRIMARY KEY, val text);
INSERT INTO val(val_id, val) VALUES
  (  1, 'foo')
, (  2, 'bar')
, (317, 'baz');

CREATE FUNCTION f_val(_id int)
  RETURNS text STABLE LANGUAGE SQL AS
'SELECT val FROM val WHERE val_id = $1';

SELECT f_val(2);  -- returns 'baz'

Поскольку plpgsql проверяет существование таблицы при создании, вам нужно создать (временную) таблицу val прежде чем вы сможете создать эту функцию, даже если временная таблица отбрасывается в конце сеанса, пока функция сохраняется. Функция вызовет исключение, если базовая таблица не будет найдена во время вызова.

Текущая схема для временных объектов идет до остальной части вашего пути search_path по умолчанию - если не указано явно явно. Вы не можете исключить временную схему из пути search_path, но сначала можете поместить другие схемы.
Злые существа ночи (с необходимыми привилегиями) могут search_path с помощью search_path и ставить другой объект с таким же именем спереди:

CREATE TABLE myschema.val (val_id int PRIMARY KEY, val text);
INSERT INTO val(val_id, val) VALUES (2, 'wrong');

SET search_path = myschema, pg_temp;

SELECT f_val(2);  -- returns 'wrong'

Это не большая угроза, так как только привилегированные пользователи могут изменять глобальные настройки. Другие пользователи могут делать это только для своей собственной сессии. Чтобы этого не происходило, установите для параметра search_path для вашей функции и схемы - укажите его в вызове:

CREATE FUNCTION f_val(_id int)
  RETURNS text STABLE LANGUAGE SQL AS
'SELECT val FROM val WHERE val_id = $1'
SET search_path = pg_temp;

Или используйте вместо этого:

... SET search_path = pg_temp, param;

Это позволит вам (или кому-либо с необходимыми привилегиями) предоставлять глобальные (постоянные) значения по умолчанию в таблице param.val...

Рассмотрим соответствующую главу руководства по созданию функций с ОПРЕДЕЛЕНИЕМ БЕЗОПАСНОСТИ.

Однако эта суперзащищенная функция не может быть "встроена" и может работать медленнее, чем более простая альтернатива жесткой схеме:

CREATE FUNCTION f_val(_id int)
  RETURNS text STABLE LANGUAGE SQL AS
'SELECT val FROM pg_temp.val WHERE val_id = $1';

Похожие ответы с большим количеством опций:

Ответ 3

В дополнение к разумным вариантам, которые уже упоминались Гордоном и Эрвином (временные таблицы, функции постоянного возврата, CTE и т.д.), вы также можете (ab) использовать механизм PostgreSQL GUC для создания глобальных, сеансовых и транзакционных уровней переменные.

См. этот предыдущий пост, в котором подробно описан подход.

Я не рекомендую это для общего использования, но он может быть полезен в узких случаях, таких как упомянутый в связанном вопросе, где плакат хотел, чтобы предоставить имя пользователя на уровне приложения для триггеров и функций.

Ответ 4

Я нашел это решение:

with vars as (
    SELECT * FROM (values(5)) as t(MY_ID)
)
SELECT * FROM users WHERE id = (SELECT MY_ID FROM vars)

Ответ 5

Я нашел смесь доступных подходов, чтобы быть лучшими:

  • Сохраните ваши переменные в таблице:
CREATE TABLE vars (
  id INT NOT NULL PRIMARY KEY DEFAULT 1,
  zipcode INT NOT NULL DEFAULT 90210,
  -- etc..
  CHECK (id = 1)
);
  • Создайте динамическую функцию, которая загружает содержимое вашей таблицы и использует ее для:
    • Re/Создайте еще одну отдельную статическую неизменяемую функцию получения.
CREATE FUNCTION generate_var_getter()
RETURNS VOID AS $$
DECLARE
  var_name TEXT;
  var_value TEXT;
  new_rows TEXT[];
  new_sql TEXT;
BEGIN
  FOR var_name IN (
    SELECT columns.column_name
    FROM information_schema.columns
    WHERE columns.table_schema = 'public'
      AND columns.table_name = 'vars'
    ORDER BY columns.ordinal_position ASC
  ) LOOP
    EXECUTE
      FORMAT('SELECT %I FROM vars LIMIT 1', var_name)
      INTO var_value;

    new_rows := ARRAY_APPEND(
      new_rows,
      FORMAT('(''%s'', %s)', var_name, var_value)
    );
  END LOOP;

  new_sql := FORMAT($sql$
    CREATE OR REPLACE FUNCTION var_get(key_in TEXT)
    RETURNS TEXT AS $config$
    DECLARE
      result NUMERIC;
    BEGIN
      result := (
        SELECT value FROM (VALUES %s)
        AS vars_tmp (key, value)
        WHERE key = key_in
      );
      RETURN result;
    END;
    $config$ LANGUAGE plpgsql IMMUTABLE;
  $sql$, ARRAY_TO_STRING(new_rows, ','));

  EXECUTE new_sql;
  RETURN;
END;
$$ LANGUAGE plpgsql;
  • Добавьте триггер обновления в свою таблицу, чтобы после изменения одной из ваших переменных вызывался generate_var_getter(), и var_get() immutable var_get() заново.
CREATE FUNCTION vars_regenerate_update()
RETURNS TRIGGER AS $$
BEGIN
  PERFORM generate_var_getter();
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_vars_regenerate_change
  AFTER INSERT OR UPDATE ON vars
  EXECUTE FUNCTION vars_regenerate_update();

Теперь вы можете легко хранить свои переменные в таблице, а также получать быстрый и неизменный доступ к ним. Лучший из двух миров:

INSERT INTO vars DEFAULT VALUES;
-- INSERT 0 1

SELECT var_get('zipcode')::INT; 
-- 90210

UPDATE vars SET zipcode = 84111;
-- UPDATE 1

SELECT var_get('zipcode')::INT;
-- 84111