Как използвате променливи на скрипт в psql?

Променливите на Postgres се създават чрез командата \set, например ...

\set myvariable value

... и след това може да бъде заменен, например, като ...

SELECT * FROM :myvariable.table1;

... or ...

SELECT * FROM table1 WHERE :myvariable IS NULL;

редактиране: От 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' да се променя от сесия на сесия. Ще бъде заменено веднъж, когато функцията е дефинирана, и след това ще бъде константа. Полезно е за скриптове, но не и за използване по време на изпълнение.

FWIW, истинският проблем беше, че бях включил точка и запетая в края на моята команда \set:

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

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

\echo :собствена_парола паролата;

Така че, когато се опитах да го използвам:

СЪЗДАВАНЕ НА РОЛЯ myrole ВХОД НЕШИФРОВАНА ПАРОЛА :owner_password NOINHERIT CREATEDB CREATEROLE VALID UNTIL 'infinity';

...разбрах това:

СЪЗДАВАНЕ НА РОЛЯ myrole ВХОД НЕШИФРОВАНА ПАРОЛА thepassword; NOINHERIT CREATEDB CREATEROLE ВАЛИДЕН ДО „безкрайност“;

Това не само не успя да зададе кавичките около литерала, но раздели командата на 2 части (втората от които беше невалидна, тъй като започна с "NOINHERIT").

Моралът на тази история: „Променливите“ на PostgreSQL всъщност са макроси, използвани при разширяване на текст, а не истински стойности. Сигурен съм, че това е полезно, но в началото е трудно.

Трябва да използвате един от процедурните езици като PL/pgSQL, а не SQL proc езика. В PL/pgSQL можете да използвате vars направо в SQL изрази. За единични кавички можете да използвате функцията quote literal.

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 инжектиране.

Друг подход е да (зло)използвате PostgreSQL GUC механизма за създаване на променливи. Вижте този предишен отговор за подробности и примери.

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

Не препоръчвам това за обща употреба, но може да бъде полезно в тесни случаи като този, споменат в свързания въпрос, където авторът искаше начин да предостави потребителското име на ниво приложение на тригери и функции.

Реших го с временна таблица.

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

По този начин имах "променлива", която можех да използвам за множество заявки, която е уникална за сесията. Имах нужда от него, за да генерирам уникални „потребителски имена“, докато все още нямам сблъсъци, ако импортирам потребители със същото потребителско име.

Намерих този въпрос и отговорите за изключително полезни, но и объркващи. Имах много проблеми да накарам цитираните променливи да работят, така че ето начина, по който го накарах:

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

По този начин можете да дефинирате променливата в един израз. Когато го използвате, единичните кавички ще бъдат вградени в променливата.

ЗАБЕЛЕЖКА! Когато сложих коментар след цитираната променлива, той беше засмукан като част от променливата, когато опитах някои от методите в други отговори. Това наистина ме прецакваше за известно време. С този метод коментарите изглежда се третират, както бихте очаквали.

Наистина ми липсва тази функция. Единственият начин да постигнете нещо подобно е да използвате функции.

Използвал съм го по два начина:

• 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)

Публикувах ново решение за това в друга нишка.

Той използва таблица за съхраняване на променливи и може да бъде актуализиран по всяко време. Статична неизменна функция за получаване се създава динамично (от друга функция), задействана от актуализация на вашата таблица. Получавате хубаво място за съхранение на маса, плюс невероятно бързите скорости на неизменен гетер.