Как вы используете скриптовые переменные в PSQL?

В MS SQL Server я создаю свои скрипты для использования настраиваемых переменных:

DECLARE @somevariable int  
SELECT @somevariable = -1

INSERT INTO foo VALUES ( @somevariable )

Затем я изменю значение @somevariable во время выполнения, в зависимости от значения, которое я хочу в конкретной ситуации. Поскольку он находится в верхней части сценария, его легко увидеть и запомнить.

Как мне сделать то же самое с клиентом PostgreSQL psql?

Источник

13 ответов

Решение

Переменные Postgres создаются с помощью команды \set, например...

\set myvariable value

... и затем может быть заменен, например, как...

SELECT * FROM :myvariable.table1;

... или же...

SELECT * FROM table1 WHERE :myvariable IS NULL;

edit: Начиная с psql 9.1, переменные могут быть расширены в кавычки, как в:

\set myvariable value 

SELECT * FROM table1 WHERE column1 = :'myvariable';

В старых версиях клиента psql:

... Если вы хотите использовать переменную в качестве значения в запросе условной строки, например...

SELECT * FROM table1 WHERE column1 = ':myvariable';

... тогда вам нужно включить кавычки в саму переменную, так как выше не будет работать. Вместо этого определите свою переменную как таковую...

\set myvariable 'value'

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

\set quoted_myvariable '\'' :myvariable '\''

Теперь у вас есть как в кавычках, так и в кавычках переменная одной строки! И вы можете сделать что-то вроде этого....

INSERT INTO :myvariable.table1 SELECT * FROM table2 WHERE column1 = :quoted_myvariable;
Источник

Последнее слово о переменных PSQL:

  1. Они не расширяются, если вы заключаете их в одинарные кавычки в операторе SQL. Таким образом, это не работает:

    SELECT * FROM foo WHERE bar = ':myvariable'

  2. Чтобы развернуть строковый литерал в операторе SQL, необходимо включить кавычки в набор переменных. Однако значение переменной уже должно быть заключено в кавычки, что означает, что вам нужен второй набор кавычек, а внутренний набор должен быть экранирован. Таким образом вам нужно:

    \set myvariable '\'somestring\''  
SELECT * FROM foo WHERE bar = :myvariable

    РЕДАКТИРОВАТЬ: начиная с PostgreSQL 9.1, вы можете написать вместо этого:

    \set myvariable somestring
SELECT * FROM foo WHERE bar = :'myvariable'
Источник

Вы можете попробовать использовать предложение WITH.

WITH vars AS (SELECT 42 AS answer, 3.14 AS appr_pi)
SELECT t.*, vars.answer, t.radius*vars.appr_pi
FROM table AS t, vars;
Источник

Специально для psql, вы можете пройти psql переменные из командной строки тоже; Вы можете передать их с -v, Вот пример использования:

$ psql -v filepath=/path/to/my/directory/mydatafile.data regress
regress=> SELECT :'filepath';
               ?column?                
---------------------------------------
 /path/to/my/directory/mydatafile.data
(1 row)

Обратите внимание, что двоеточие не заключено в кавычки, затем имя переменной, в которой он находится, заключено в кавычки Странный синтаксис, я знаю. Это работает только в psql; это не сработает, скажем, в PgAdmin-III.

Эта замена происходит во время обработки ввода в psql, поэтому вы не можете (скажем) определить функцию, которая использует :'filepath' и ожидаем значение :'filepath' переключаться с сессии на сессию. Он будет подставлен один раз, когда функция будет определена, и после этого станет константой. Это полезно для сценариев, но не для использования во время выполнения.

Источник

Кстати, реальная проблема заключалась в том, что я добавил точку с запятой в конце моей команды \set:

\set owner_password 'thepassword';

Точка с запятой была интерпретирована как фактический символ в переменной:

\ echo: owner_password thepassword;

Итак, когда я попытался использовать это:

СОЗДАТЬ РОЛЬ myrole ВХОД НЕПИСАННЫЙ ПАРОЛЬ:owner_password NOINHERIT CREATEDB CREATEROLE ДЕЙСТВИТЕЛЬНО ДО 'infinity';

... я получил это:

СОЗДАЙТЕ РОЛЬ myrole ВХОД НЕПИСАННЫЙ ПАРОЛЬ thepassword; NOINHERIT CREATEDB CREATEROLE Действителен до 'бесконечности';

Это не только не позволило установить кавычки вокруг литерала, но и разделило команду на 2 части (вторая из которых была недействительной, поскольку начиналась с "NOINHERIT").

Мораль этой истории: "переменные" PostgreSQL - это действительно макросы, используемые в раскрытии текста, а не истинные значения. Я уверен, что это пригодится, но сначала это сложно.

Источник

postgres (начиная с версии 9.0) разрешает анонимные блоки на любом из поддерживаемых серверных языков сценариев

DO '
DECLARE somevariable int = -1;
BEGIN
INSERT INTO foo VALUES ( somevariable );
END
' ;

http://www.postgresql.org/docs/current/static/sql-do.html

Поскольку все находится внутри строки, внешние строковые переменные, подлежащие замене, должны быть экранированы и заключены в кавычки дважды. Использование долларовых кавычек вместо этого не даст полной защиты от SQL-инъекций.

Источник

Вам нужно использовать один из процедурных языков, таких как PL/pgSQL, а не язык процедур SQL. В PL/pgSQL вы можете использовать переменные прямо в инструкциях SQL. Для одинарных кавычек вы можете использовать буквальную функцию quote.

Источник

Я решил это с помощью временной таблицы.

CREATE TEMP TABLE temp_session_variables (
    "sessionSalt" TEXT
);
INSERT INTO temp_session_variables ("sessionSalt") VALUES (current_timestamp || RANDOM()::TEXT);

Таким образом, у меня была "переменная", которую я мог использовать для нескольких запросов, которая является уникальной для сеанса. Мне нужно было генерировать уникальные "имена пользователей", но при этом не возникало коллизий при импорте пользователей с одинаковыми именами.

Источник

Другой подход заключается в том, чтобы (ab) использовать механизм PostgreSQL GUC для создания переменных. Смотрите этот предыдущий ответ для деталей и примеров.

Вы объявляете GUC в postgresql.conf, затем измените его значение во время выполнения с SET команды и получить его значение с current_setting(...),

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

Источник

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

Я использовал его двумя способами:

  • функции perl, которые используют переменную $_SHARED
  • сохранить ваши переменные в таблице

Версия Perl:

   CREATE FUNCTION var(name text, val text) RETURNS void AS $$
        $_SHARED{$_[0]} = $_[1];
   $$ LANGUAGE plperl;
   CREATE FUNCTION var(name text) RETURNS text AS $$
        return $_SHARED{$_[0]};
   $$ LANGUAGE plperl;

Версия стола:

CREATE TABLE var (
  sess bigint NOT NULL,
  key varchar NOT NULL,
  val varchar,
  CONSTRAINT var_pkey PRIMARY KEY (sess, key)
);
CREATE FUNCTION var(key varchar, val anyelement) RETURNS void AS $$
  DELETE FROM var WHERE sess = pg_backend_pid() AND key = $1;
  INSERT INTO var (sess, key, val) VALUES (sessid(), $1, $2::varchar);
$$ LANGUAGE 'sql';

CREATE FUNCTION var(varname varchar) RETURNS varchar AS $$
  SELECT val FROM var WHERE sess = pg_backend_pid() AND key = $1;
$$ LANGUAGE 'sql';

Заметки:

  • plperlu быстрее, чем Perl
  • pg_backend_pid - не лучшая идентификация сеанса, рассмотрите возможность использования pid в сочетании с backend_start из pg_stat_activity
  • эта версия таблицы также плоха, потому что вы должны периодически очищать ее (а не удалять текущие переменные сеанса)
Источник

Переменные в psql сосать. Если вы хотите объявить целое число, вы должны ввести целое число, затем выполнить возврат каретки, а затем завершить оператор точкой с запятой. Заметим:

Допустим, я хочу объявить целочисленную переменную my_var и вставьте его в стол test:

Пример таблицы test:

thedatabase=# \d test;
                         Table "public.test"
 Column |  Type   |                     Modifiers                     
--------+---------+---------------------------------------------------
 id     | integer | not null default nextval('test_id_seq'::regclass)
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

Понятно, что ничего в этой таблице пока нет:

thedatabase=# select * from test;
 id 
----
(0 rows)

Мы объявляем переменную. Обратите внимание на точку с запятой в следующей строке!

thedatabase=# \set my_var 999
thedatabase=# ;

Теперь мы можем вставить. Мы должны использовать это странно:''"выглядит синтаксис:

thedatabase=# insert into test(id) values (:'my_var');
INSERT 0 1

Это сработало!

thedatabase=# select * from test;
 id  
-----
 999
(1 row)

Объяснение:

Итак... что произойдет, если у нас нет точки с запятой в следующей строке? Переменная? Посмотри:

Мы заявляем my_var без новой строки.

thedatabase=# \set my_var 999;

Давайте выберем my_var,

thedatabase=# select :'my_var';
 ?column? 
----------
 999;
(1 row)

Что это за хрень? Это не целое число, это строка 999;!

thedatabase=# select 999;
 ?column? 
----------
      999
(1 row)
Источник

Я нашел этот вопрос и ответы чрезвычайно полезными, но и запутанными. У меня было много проблем, чтобы заставить работать переменные в кавычках, поэтому вот как я это сделал:

\set deployment_user username    -- username
\set deployment_pass '\'string_password\''
ALTER USER :deployment_user WITH PASSWORD :deployment_pass;

Таким образом, вы можете определить переменную в одном выражении. Когда вы используете его, одинарные кавычки будут встроены в переменную.

НОТА! Когда я помещаю комментарий после указанной в кавычках переменной, он засасывается как часть переменной, когда я пробовал некоторые методы в других ответах. Это действительно меня немного смутило. С помощью этого метода комментарии выглядят так, как вы ожидаете.

Источник

Я разместил новое решение для этого в другой теме.

Он использует таблицу для хранения переменных и может быть обновлен в любое время. Статическая неизменяемая функция-получатель создается динамически (другой функцией) и запускается обновлением таблицы. Вы получаете отличное хранение стола, а также невероятно высокие скорости неизменного добытчика.

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