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

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

MY_ID = 5;
SELECT * FROM users WHERE id = MY_ID;
Источник

7 ответов

Решение

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

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

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

Источник

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

За один запрос

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

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

Вы могли бы создать простойIMMUTABLEфункция для этого:

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

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

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и поместите другой объект с тем же именем впереди:

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...

Рассмотрим соответствующую главу руководства по созданию функций с помощью SECURITY DEFINER.

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

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

Связанные ответы с большим количеством опций:

Источник

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

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

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

Источник

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

  • Сохраните ваши переменные в таблице:
CREATE TABLE vars (
  id INT NOT NULL PRIMARY KEY DEFAULT 1,
  zipcode INT NOT NULL DEFAULT 90210,
  -- etc..
  CHECK (id = 1)
);
  • Создайте динамическую функцию, которая загружает содержимое вашей таблицы и использует ее для:
    • Пере / Создайте еще одну отдельную статическую неизменяемую функцию получения.
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() функция воссоздана.
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
Источник

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

with vars as (
    SELECT * FROM (values(5)) as t(MY_ID)
)
SELECT * FROM users WHERE id = (SELECT MY_ID FROM vars)
Источник

Когда в вашем запросе используется «GROUP BY»:

      WITH const AS (
     select 5                 as MY_ID,
            '2022-03-1'::date as MY_DAY)

SELECT u.user_group,
       COUNT(*),
       const.MY_DAY
FROM users u
CROSS JOIN const

WHERE 1=1
GROUP BY u.user_group, const.MY_ID, const.MY_DAY

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

Без ГРУППИРОВКИ:

      WITH const AS (
     select 5 as MY_ID)

SELECT u.* FROM users u
CROSS JOIN const

WHERE u.id = const.MY_ID

кредиты @GordonLinoff

Без GROUP BY и без конфликтов имен столбцов:

      WITH const AS (
     select 5 as MY_ID)

SELECT users.* FROM users
CROSS JOIN const

WHERE id = MY_ID
Источник

Если вы хотите сохранить свою константу между сеансами и не хотите использовать таблицу, вы можете установить пользовательские настройки для конкретной базы данных или роли. Такие настройки можно переопределить на уровне сеанса или транзакции. Но это легко прочитать, перейдя непосредственно к pg_db_role_settingsсистемный каталог:

      create function pg_db_setting(pg_setting_name$ text, pg_role$ regrole = 0)
    returns text
    stable
--    security definer
    return (
        select
            regexp_replace(expanded_settings.raw_setting, E'^[^=]+=', '')
        from
            pg_catalog.pg_db_role_setting
        inner join
            pg_catalog.pg_database
            on pg_database.oid = pg_db_role_setting.setdatabase
        cross join lateral
            unnest(pg_db_role_setting.setconfig) as expanded_settings(raw_setting)
        where
            pg_database.datname = current_database()
            and pg_db_role_setting.setrole = coalesce(
                pg_role$,
                0  -- 0 means “not role-specific”
            )
            and expanded_settings.raw_setting like pg_setting_name$ || '=%'
        limit 1
    );

Эта функция скопирована (с разрешения) с моего расширение.

Вот пример, взятый из README) того, как его использовать:

      CREATE DATABASE mydb;
CONNECT TO mydb
CREATE ROLE myrole;
ALTER DATABASE mydb
    SET app.settings.bla = 1::text;
ALTER ROLE myrole
    IN DATABASE mydb
    SET app.settings.bla = 2::text;
SET ROLE myrole;
SET app.settings.bla TO 3::text;
SELECT current_setting('app.settings.bla', true);  -- '3'
SELECT pg_db_role_setting('app.settings.bla');  -- '1'
SELECT pg_db_role_setting('app.settings.bla', current_user);  -- '2'

Если вы предпочитаете, тестовая процедура немного сложнее:

      CREATE OR REPLACE PROCEDURE ext.test__pg_db_setting()
 LANGUAGE plpgsql
 SET "plpgsql.check_asserts" TO 'true'
 SET "pg_readme.include_this_routine_definition" TO 'true'
AS $procedure$
begin
    execute 'ALTER DATABASE ' || current_database()
        || ' SET pg_safer_settings.test_pg_db_setting = ''foo''';
    assert pg_db_setting('pg_safer_settings.test_pg_db_setting') = 'foo';

    set pg_safer_settings.settings.test_pg_db_setting = 'bar';
    assert pg_db_setting('pg_safer_settings.test_pg_db_setting') = 'foo';

    assert pg_db_setting('pg_safer_settings.unknown_setting') is null;

    create role __test_role;
    execute 'ALTER ROLE __test_role IN DATABASE ' || current_database()
        || ' SET pg_safer_settings.test_pg_db_setting = ''foobar''';
    assert pg_db_setting('pg_safer_settings.test_pg_db_setting', '__test_role') = 'foobar';
    assert pg_db_setting('pg_safer_settings.test_pg_db_setting') = 'foo';

    raise transaction_rollback;
exception
    when transaction_rollback then
end;
$procedure$

pg_safer_settingsтакже предоставляет более сложный вариант шаблона, предложенного @Brev в его ответе .

Источник
Другие вопросы по тегам