Jak używać zmiennych skryptowych w psql?

Ostatnie słowo o zmiennych PSQL:

1. Nie rozwijają się, jeśli zamkniesz je w pojedynczych cudzysłowach w instrukcji SQL. Więc to nie działa:

   SELECT * FROM foo WHERE bar = ':myvariable'
   

2. Aby rozwinąć ciąg znaków w instrukcji SQL, musisz uwzględnić cudzysłowy w zestawie zmiennych. Jednak wartość zmiennej musi być już zamknięta w cudzysłowach, co oznacza, że potrzebny jest drugi zestaw cudzysłowów, a wewnętrzny zestaw musi być zabezpieczony. Tak więc ty need:

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

   EDIT: zaczynając od PostgreSQL 9.1, możesz zamiast tego napisać:

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

Możesz spróbować użyć z klauzulą.

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;

Specjalnie dla psql, Możesz również przekazać psql zmienne z linii poleceń; możesz przekazać je za pomocą -v. Oto przykład użycia:

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

Zauważ, że dwukropek nie jest cytowany, wtedy nazwa zmiennej jest cytowana. Dziwna składnia, wiem. To działa tylko w psql; nie będzie działać w (powiedzmy) PgAdmin-III.

To zastąpienie ma miejsce podczas przetwarzania danych wejściowych w psql, więc nie można (powiedzieć) zdefiniować funkcji, która używa :'filepath' i oczekiwać, że wartość :'filepath' zmieni się z sesji na sesja. Zostanie podstawiona raz, gdy funkcja zostanie zdefiniowana, a następnie będzie stałą. Jest to przydatne do skryptów, ale nie do użytku w trybie runtime.

FWIW, prawdziwym problemem było to, że na końcu polecenia \set umieściłem średnik:

\ set owner_password 'thepassword';

Średnik został zinterpretowany jako znak rzeczywisty w zmiennej:

\ echo: owner_password thepassword;

Więc kiedy próbowałem go użyć:

CREATE ROLE myrole LOGIN UNENCRYPTED PASSWORD :owner_password NOINHERIT CREATEDB CREATEROLE VALID UNTIL 'infinity';

...Mam to:

CREATE ROLE myrole LOGIN UNENCRYPTED PASSWORD thepassword; NOINHERIT CREATEDB CREATEROLE VALID UNTIL 'infinity';

Że nie tylko nie udało się ustawić cudzysłowów wokół literału, ale podzielić polecenie na 2 części (druga z nich była nieprawidłowa, ponieważ zaczynała się od "NOINHERIT").

Morał tej historii: "zmienne" PostgreSQL są tak naprawdę makrami używanymi w rozszerzaniu tekstu, a nie prawdziwymi wartościami. Jestem pewien, że to się przyda, ale na początku jest trudne.

Musisz użyć jednego z języków proceduralnych, takich jak PL / pgSQL, a nie języka proc SQL. W PL/pgSQL możesz używać vars bezpośrednio w poleceniach SQL. Dla pojedynczych cudzysłowów można użyć funkcji cytowania literalnego.

Postgres (od wersji 9.0) pozwala na anonimowe bloki w dowolnym z obsługiwanych języków skryptowych po stronie serwera

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

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

Ponieważ wszystko znajduje się w łańcuchu, zewnętrzne zmienne łańcuchowe, które są podstawiane, będą musiały zostać dwukrotnie zacytowane. Zamiast tego używanie notowań dolarowych nie daje pełnej ochrony przed SQL injection.

Innym podejściem jest (ab)użycie mechanizmu Guc PostgreSQL do tworzenia zmiennych. Zobacz Ta wcześniejsza odpowiedź Po szczegóły i przykłady.

Deklarujesz GUC w postgresql.conf, następnie zmieniasz jego wartość w czasie wykonywania poleceniami SET i otrzymujesz jego wartość za pomocą current_setting(...).

Nie polecam tego do ogólnego użytku, ale może być przydatny w wąskich przypadkach, takich jak ten wymieniony w linked question, gdzie plakat chciał sposób na podanie nazwy użytkownika na poziomie aplikacji do wyzwalaczy i funkcje.

Rozwiązałem to za pomocą tabeli temp.

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

W ten sposób miałem "zmienną", której mogłem użyć w wielu zapytaniach, która jest unikalna dla sesji. Potrzebowałem go do generowania unikalnych "nazw użytkowników", jednocześnie nie mając kolizji, jeśli importujesz użytkowników o tej samej nazwie użytkownika.

Znalazłem to pytanie i odpowiedzi niezwykle przydatne, ale również mylące. Miałem wiele problemów z uruchomieniem cytowanych zmiennych, więc oto sposób, w jaki to działa:

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

W ten sposób można zdefiniować zmienną w jednym poleceniu. Gdy go użyjesz, pojedyncze cudzysłowy zostaną osadzone w zmiennej.

Uwaga! Kiedy umieściłem komentarz po cytowanej zmiennej, został wessany jako część zmiennej, gdy próbowałem niektórych metod w innych odpowiedziach. To było naprawdę przez jakiś czas mnie wkurwiał. Dzięki tej metodzie komentarze wydają się być traktowane tak, jak można się spodziewać.

Brakuje mi tej funkcji. Jedynym sposobem na osiągnięcie czegoś podobnego jest użycie funkcji.

Użyłem go na dwa sposoby:

• Funkcje Perla, które używają zmiennej $_SHARED
• Zapisz swoje zmienne w tabeli

Wersja Perla:

   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;

Wersja Tabeli:

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';

Uwagi:

• plperlu jest szybszy niż perl
• pg_backend_pid nie jest najlepszą identyfikacją sesji, rozważ użycie pid w połączeniu z backend_start z pg_stat_activity
• Ta wersja tabeli jest również zła, ponieważ musisz wyczyścić to od czasu do czasu (i nie usuwać aktualnie działających zmiennych sesji)