Comment utilisez-vous les variables de script dans psql?

un dernier mot sur les variables PSQL:

1. ils ne s'étendent pas si vous les enfermez dans des guillemets simples dans la déclaration SQL. Ainsi, cela ne fonctionne pas:

   SELECT * FROM foo WHERE bar = ':myvariable'
   

2. pour étendre à une chaîne littérale dans une instruction SQL, vous devez inclure les guillemets dans l'ensemble de variables. Cependant, la valeur de la variable doit déjà être incluse dans les guillemets, ce qui signifie que vous avez besoin d'une seconde ensemble de citations, et l'intérieur a échappé. Vous avez donc besoin de:

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

   EDIT : en commençant par PostgreSQL 9.1, vous pouvez écrire à la place:

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

vous pouvez essayer d'utiliser une avec clause.

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;

spécifiquement pour psql , vous pouvez passer psql variables de la ligne de commande aussi; vous pouvez les passer avec -v . Voici un exemple d'usage:

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

notez que le deux-points n'est pas coté, alors la variable name its self est citée. Étrange syntaxe, je sais. Cela ne fonctionne qu'en psql; cela ne fonctionnera pas en (disons) PgAdmin-III.

cette substitution se produit pendant le traitement d'entrée dans psql, donc vous ne pouvez pas (dire) définir une fonction qui utilise :'filepath' et s'attend à ce que la valeur de :'filepath' change d'une session à l'autre. Il sera substitué une fois, lorsque la fonction est définie, et alors sera une constante après cela. C'est utile pour les scripts, mais pas d'exécution de l'utiliser.

FWIW, le vrai problème était que j'avais inclus un point-virgule à la fin de ma commande \set:

\owner_password 'thepassword';

le point-virgule a été interprété comme un caractère réel dans la variable:

\echo: owner_password the password;

donc quand j'ai essayé de l'utiliser:

CRÉER UN RÔLE myrole LOGIN mot de passe non chiffré: owner_password NOINHERIT CREATEDB CREATEROLE VALID UNTIL 'infinity';

...J'ai reçu ceci:

créer un rôle MYROLE LOGIN mot de passe non crypté the password; NOINHERIT CREATEDB CREATEROLE VALID UNTIL 'infinity';

qui non seulement n'a pas réussi à placer les citations autour de la littérale, mais a divisé la commande en 2 parties (dont la seconde était invalide comme il a commencé avec "NOINHERIT").

La morale de cette histoire: PostgreSQL "variables" sont en réalité des macros utilisées dans l'expansion du texte, pas les vraies valeurs. Je suis sûr que c'est pratique, mais c'est délicat au début.

vous devez utiliser un des langages de procédure tels que PL/pgSQL pas le langage SQL proc. Dans PL / pgSQL, vous pouvez utiliser vars directement dans les instructions SQL. Pour les citations simples, vous pouvez utiliser la fonction de citation littérale.

postgres (depuis la version 9.0) permet des blocs anonymes dans n'importe lequel des langages de script côté serveur pris en charge

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

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

comme tout est à l'intérieur d'une chaîne de caractères, les variables externes de chaîne substituées devront être échappées et citées deux fois. L'utilisation de Dollar quoting à la place ne donnera pas une protection complète contre L'injection SQL.

une autre approche consiste à (ab)utiliser le mécanisme GUC PostgreSQL pour créer des variables. Voir cette réponse antérieure pour plus de détails et des exemples.

vous déclarez le GUC dans postgresql.conf , puis changez sa valeur à l'exécution avec les commandes SET et obtenez sa valeur avec current_setting(...) .

Je ne le recommande pas pour un usage général, mais il pourrait être utile dans des cas étroits comme celui mentionné dans la question liée, où le poster voulait un moyen de fournir le nom d'utilisateur au niveau de l'application pour les déclencheurs et les fonctions.

Je l'ai résolu avec une table de travail.

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

de cette façon, j'avais une" variable " que je pouvais utiliser sur plusieurs requêtes, qui est unique pour la session. J'en avais besoin pour générer des "noms d'utilisateurs" uniques tout en n'ayant toujours pas de collisions si j'importe des utilisateurs avec le même nom d'utilisateur.

j'ai trouvé cette question et les réponses extrêmement utiles, mais aussi déroutantes. J'ai eu beaucoup de mal à obtenir des variables citées pour fonctionner, alors voici la façon dont je l'ai fait fonctionner:

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

de cette façon, vous pouvez définir la variable dans un énoncé. Lorsque vous l'utilisez, apostrophes sera intégré dans la variable.

NOTE! Quand j'ai mis un commentaire après la variable Citée il a été aspiré dans le cadre de la variable lorsque j'ai essayé certains de la méthodes dans d'autres réponses. C'était vraiment le vissage de moi pendant un moment. Avec cette méthode, les commentaires semblent être traitée comme vous le souhaitez.

cette fonctionnalité me manque vraiment. La seule façon d'obtenir quelque chose de similaire est d'utiliser des fonctions.

je l'ai utilisé de deux façons:

• perl fonctions qui utilisent $_SHARED variable
• stockez vos variables dans le tableau

Perl version:

   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;

version Table:

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 = ;
  INSERT INTO var (sess, key, val) VALUES (sessid(), , ::varchar);
$$ LANGUAGE 'sql';

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

Notes:

• plperlu est plus rapide que perl
• pg_backend_pid n'est pas la meilleure identification de la session, pensez à utiliser pid combiné avec backend_start de pg_stat_activity
• cette version de tableau est également mauvais parce que vous devez clarifier ce est de temps en temps (et de ne pas supprimer actuellement les variables de session de travail)