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

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

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

   SELECT * FROM foo WHERE bar = ':myvariable'
   

2. Чтобы преобразовать в строковый 9X_variables литерал в операторе SQL, вы должны включить 9X_sql-postgres кавычки в набор переменных. Однако значение 9X_postgresql переменной уже должно быть заключено в кавычки, а 9X_select-statement это значит, что вам нужен второй набор кавычек, а 9X_sql внутренний набор должен быть экранирован. Таким 9X_vars образом, вам нужно:

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

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

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

Вы можете попробовать использовать предложение 9X_sql-query 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 вы также можете передавать 9X_vars переменные psql из командной строки; вы можете 9X_sql-query передать их с помощью -v. Вот пример использования:

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

Обратите 9X_sql-select внимание, что двоеточие не заключено в кавычки, тогда 9X_variable указывается само имя переменной. Странный 9X_pgsql синтаксис, я знаю. Это работает только в 9X_pgsql psql; это не будет работать (скажем) в PgAdmin-III.

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

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

\set owner_password 'пароль';

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

\echo :owner_password пароль;

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

CREATE ROLE 9X_sql-query myrole ВХОД НЕЗАШИФРОВАННЫЙ ПАРОЛЬ :owner_password 9X_psql NOINHERIT CREATEDB CREATEROLE ДЕЙСТВИТЕЛЬНО 9X_sql-syntax ДО «бесконечности»;

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

СОЗДАТЬ 9X_postgres РОЛЬ myrole ВХОД НЕШИФРОВАННЫЙ ПАРОЛЬ thepassword; NOINHERIT 9X_sql-query CREATEDB CREATEROLE ДЕЙСТВИТЕЛЬНО ДО «бесконечности»;

Это 9X_select-statement не только не заключало литерал в кавычки, но 9X_select-statement и разделяло команду на 2 части (вторая из 9X_sqlselect которых была недействительной, так как начиналась 9X_sql-statement с "NOINHERIT").

Мораль этой истории: «переменные» PostgreSQL 9X_postgres на самом деле являются макросами, используемыми 9X_psql для раскрытия текста, а не истинными значениями. Я 9X_sql-statement уверен, что это удобно, но поначалу это 9X_postgres сложно.

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

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

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

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

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

Другой подход - (ab) использовать механизм 9X_sql-statement PostgreSQL GUC для создания переменных. См. this prior answer для 9X_variable подробностей и примеров.

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

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

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

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

Таким 9X_sqlselect образом, у меня была «переменная», которую 9X_pgsql я мог использовать для нескольких запросов, уникальную 9X_variable для сеанса. Мне нужно, чтобы он генерировал 9X_variables уникальные «имена пользователей», но при 9X_variables этом не сталкивался с конфликтами при импорте 9X_variables пользователей с тем же именем пользователя.

Я нашел этот вопрос и ответы чрезвычайно 9X_sql-postgres полезными, но также сбивающими с толку. У 9X_sql-statement меня было много проблем с тем, чтобы заставить 9X_sql-query работать цитируемые переменные, поэтому 9X_postgresql вот как у меня это работает:

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

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

ПРИМЕЧАНИЕ! Когда я помещал 9X_pgsql комментарий после цитируемой переменной, он 9X_select-statement втягивался как часть переменной, когда я 9X_select-statement пробовал некоторые методы в других ответах. Это 9X_psql действительно какое-то время меня облажало. С 9X_postgresql помощью этого метода комментарии обрабатываются 9X_pgsql так, как и следовало ожидать.

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

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

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

Версия 9X_variables 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 - отстой. Если вы хотите объявить 9X_sql-syntax целое число, вы должны ввести целое число, затем 9X_sql-statement выполнить возврат каретки и завершить оператор 9X_sqlselect точкой с запятой. Обратите внимание:

Допустим, я 9X_sql хочу объявить целочисленную переменную my_var и 9X_postgres вставить ее в таблицу 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)

Очевидно, в 9X_variables этой таблице пока ничего нет:

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

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

thedatabase=# \set my_var 999
thedatabase=# ;

Теперь мы можем вставить. Мы 9X_select-statement должны использовать этот странный синтаксис 9X_sql-select ":''":

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

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

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

Пояснение:

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

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

thedatabase=# \set my_var 999;

Давайте выберем 9X_postgresql my_var.

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

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

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

Я опубликовал новое решение для этого on another thread.

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