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

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

DECLARE @somevariable int SELECT @somevariable = -1 INSERT INTO foo VALUES ( @somevariable ) 

Затем 9X_postgresql я изменю значение @somevariable во время выполнения в 9X_sql-statement зависимости от значения, которое мне нужно 9X_sqlselect в конкретной ситуации. Поскольку он находится 9X_psql в верхней части сценария, его легко увидеть 9X_psql и запомнить.

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

176
2

  • FWIW, оператор \ set _, по-видимому, ...
13
Общее количество ответов: 13

Ответ #1

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

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

\set myvariable value 

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

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:

... Если 9X_pgsql вы хотите использовать переменную в качестве 9X_psql значения в условном строковом запросе, например 9X_select-statement ...

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

... тогда вам нужно включить кавычки 9X_sql-postgres в саму переменную, так как приведенное выше 9X_select-statement не сработает. Вместо этого определите вашу 9X_variable переменную как таковую ...

\set myvariable 'value' 

Однако, если вы, как 9X_vars и я, столкнетесь с ситуацией, когда хотите 9X_postgresql создать строку из существующей переменной, я 9X_sql-syntax обнаружил, что уловка будет в следующем 9X_sql-postgres ...

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

Теперь у вас есть переменная в кавычках 9X_postgres и без кавычек одной и той же строки! А можно 9X_vars сделать что-то вроде этого ....

INSERT INTO :myvariable.table1 SELECT * FROM table2 WHERE column1 = :quoted_myvariable; 

216
8

  • \ set работает как макрос C. Вы не можете использовать его для хранения результата функци ...

Ответ #2

Ответ на вопрос: Как вы используете переменные сценария в 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' 

72
0

Ответ #3

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

Вы можете попробовать использовать предложение 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; 

68
3

  • Может ли кто-нибудь подсказать, ...

Ответ #4

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

Специально для 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 во время выполнения.

41
1

  • переменные psql, например : 'filepath', вы указали: «Обратите внимание, что двоеточие не заключено в кавычки, тогда имя переменной - это ...

Ответ #5

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

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 сложно.

15
0

Ответ #6

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

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-инъекций.

13
0

Ответ #7

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

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

11
2

  • plpgsql (теперь) можно использовать в postgres ...

Ответ #8

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

Другой подход - (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 триггеров и функций.

5
0

Ответ #9

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

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

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 пользователей с тем же именем пользователя.

5
0

Ответ #10

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

Я нашел этот вопрос и ответы чрезвычайно 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 так, как и следовало ожидать.

3
0

Ответ #11

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

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

2
0

Ответ #12

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

Переменные в 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) 

1
1

  • Причина, по которой точка с запятой делает для вас неожиданные вещи, заключается в том, что точка с запятой ...

Ответ #13

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

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

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

1
0