Comment utiliser le script de variables dans psql?

Dans MS SQL Server, j'ai créer mes scripts à utiliser personnalisable variables:

DECLARE @somevariable int  
SELECT @somevariable = -1

INSERT INTO foo VALUES ( @somevariable )

Je vais donc modifier la valeur de @somevariable au moment de l'exécution, en fonction de la valeur que je veux dans la situation particulière. Depuis, il est au sommet du script, il est facile de voir et de se souvenir.

Comment dois-je faire la même chose avec le client PostgreSQL psql?

  • FWIW, l' \opérateur d'ensemble semble être lié à la psql outil de ligne de commande, ne pas pgsql langage de commandes. J'ai peut-être tort.
  • Quelle est la version de Postgres êtes-vous?
InformationsquelleAutor Craig Walker | 2008-08-31
  1. 165

    Postgres variables sont créées par le biais de l' \set de commande, par exemple ...

    \set myvariable value

    ... et puis peut être substitué, par exemple, que ...

    SELECT * FROM :myvariable.table1;

    ... ou ...

    SELECT * FROM table1 WHERE :myvariable IS NULL;

    edit: Comme de psql 9.1, les variables peuvent être élargie entre guillemets, comme dans: 

    \set myvariable value 

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

    Dans les anciennes versions de la psql client:

    ... Si vous souhaitez utiliser la variable de la valeur d'un conditionnel chaîne de requête, comme ...

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

    ... ensuite, vous devez inclure les guillemets dans la variable elle-même comme le ci-dessus ne fonctionnera pas. Au lieu de définir la variable en tant que tel ...

    \set myvariable 'value'

    Toutefois, si, comme moi, vous avez couru dans une situation dans laquelle vous avez voulu faire une chaîne à partir d'une variable existante, j'ai trouvé le truc pour être présent ...

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

    Vous avez maintenant une cotées et non cotées de la variable de la même chaîne! Et vous pouvez faire quelque chose comme cela ....

    INSERT INTO :myvariable.table1 SELECT * FROM table2 WHERE column1 = :quoted_myvariable;
    • \set est seulement pour psql outil, vous ne pouvez pas l'utiliser dans des procédures stockées!
    • l'OP a demandé à propos de script, pas procédure
    • Cette réponse mélanges psql méta-commandes \set avec les commandes PostgreSQL dans une confusion de la mode.
    • Comme de postgresql 9.1, dans psql, vous pouvez maintenant utiliser: "variable" de l'avoir correctement cité comme une valeur pour vous, ou :"variable" pour l'utiliser comme identificateur.
    • Plus que 3 ans plus tard, cette réponse encore prouvé incroyablement utile!
    • \set myvariable 'value' n' pas inclure un devis à l'intérieur de la variable, contrairement à ce que cette réponse dit. En cas de doute, utilisez \echo :myvariable dans psql pour afficher la valeur indépendamment de toute requête.
    • Le second \set quoted_myvariable '\'' :myvariable '\'' vous donne les guillemets dans la variable.
    • \jeu fonctionne comme un C macro. Vous ne pouvez pas l'utiliser pour stocker le résultat d'une fonction, comme RANDOM() ou().
    • \set myvar 'value' ne fonctionne pas de la façon dont vous décrivez. Vous devez utiliser \set myvar '\'value\''
    • au lieu de citer, vous pouvez utiliser une fonte trop: \set mavariable abcd, puis sélectionnez le col de la table où attr = :mavariable::varchar, par exemple
    • Ou utiliser :'myvar' comme @Balle + suggère
    • C'est une mauvaise pratique de fournir des code de travail: ':mavariable'. - Je voir le code et de ne pas lire les commentaires que c'est un mauvais code. Donc perdu un peu de temps pour détecter un bug et révoquer mon script de collègues. Une façon de mieux, c'est vous donner le code est correct.
    • citant avec $$ fonctionne aussi - \set quoted_myvariable $$':myvariable'$$
    • aussi la variante avec les tableaux: 1. \set args $$'{"text", "text", "text", "text"}'$$::text[] est la même que \set args ARRAY[$$'text'$$, $$'text'$$, $$'text'$$, $$'text'$$]

    InformationsquelleAutor crowmagnumb
  2. 58

    Un dernier mot sur PSQL variables:

    1. Ils ne développent pas si vous placez entre guillemets simples dans l'instruction SQL.
      Donc cela ne fonctionne pas:

      SELECT * FROM foo WHERE bar = ':myvariable'

    2. De développer une chaîne de caractères littérale dans une instruction SQL, vous devez inclure les guillemets dans la variable. Toutefois, la valeur de la variable a déjà à être entourées de guillemets, ce qui signifie que vous avez besoin d'un deuxième ensemble de citations, et l'intérieur a échappé. Ainsi, vous avez besoin de:

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

      MODIFIER: à partir de PostgreSQL 9.1, vous pouvez écrire à la place:

      \set myvariable somestring
SELECT * FROM foo WHERE bar = :'myvariable'
    • Bravo pour :'myvariable'
    • Exactement ce que je cherchais!
    InformationsquelleAutor Craig Walker
  3. 40

    Vous pouvez essayer d'utiliser un 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;
    • De cette façon, est surtout pratique lorsque vous êtes en utilisant les mêmes valeurs calculées à quelques reprises dans votre requête.
    • Il ne semble pas fonctionner dans un WHERE clause. fair warning.
    • Contrairement à Bryce, il semble bien fonctionner pour moi. CREATE TABLE test (name VARCHAR, age INT); INSERT INTO test (name, age) VALUES ('Jack', 21), ('Jill', 20); WITH vars AS (SELECT N'Jack' AS name, 21 AS age) SELECT test.* FROM test, vars WHERE test.name = vars.name and test.age = vars.age; Ouputs Jack et Jack de l'âge, comme prévu.
    • Pour beaucoup d'usages, en particulier dans le contexte d'une application web framework comme Python Flacon, c'est la meilleure solution pour la réutilisation des complexes des valeurs calculées à l'intérieur d'une seule requête.
    • Quelqu'un peut-il suggérer comment cela pourrait fonctionner dans un insert?
    • la table t(x integer); insert into t(x) with sub as (select 999 as num) select num from sub; select * from t;

    InformationsquelleAutor skaurus
  4. 28

    Spécifiquement pour psql, vous pouvez passer psql variables à partir de la ligne de commande trop; vous pouvez les passer avec -v. Voici un exemple d'utilisation:

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

    Noter que le côlon est non cotées, alors la variable nom lui-même est cité. Étrange syntaxe, je sais. Cela ne fonctionne que dans psql; il ne travaille pas (dire) PgAdmin III.

    Cette substitution se produit pendant le traitement de l'entrée dans psql, de sorte que vous ne pouvez pas (dire) définir une fonction qui utilise :'filepath' et attendre que la valeur de :'filepath' de changer de session à l'autre. Il va être remplacé une fois, lorsque la fonction est définie, puis sera une constante par la suite. C'est utile pour les scripts, mais pas d'exécution de l'utiliser.

    • psql variables, par exemple :'chemin', vous l'avez souligné: "on Remarque que le côlon est non cotées, alors la variable nom lui-même est cité." Merci! Vous! J'ai déjà mis un tas de front en forme de bosses dans mon bureau pour essayer de faire ce travail, et que vous venez de m'a sauvé une tonne de plus. Exactement ce dont j'avais besoin pour quelques scripts.
    InformationsquelleAutor Craig Ringer
  5. 12

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

    \set owner_password 'thepassword';

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

    \echo :owner_password
    thepassword;

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

    CRÉER un RÔLE myrole LOGIN MOT de passe non crypté :owner_password NOINHERIT CREATEDB CREATEROLE VALABLE JUSQU 'l'infini';

    ...J'ai obtenu ceci:

    CRÉER un RÔLE myrole LOGIN MOT de passe non crypté thepassword; NOINHERIT CREATEDB CREATEROLE VALABLE JUSQU 'l'infini';

    Que non seulement échoué à définir les guillemets autour de la traduction littérale, mais de fractionner la commande en 2 parties (la seconde n'était pas valide 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 cela est pratique, mais c'est difficile au premier abord.

    InformationsquelleAutor Craig Walker
  6. 12

    Vous devez utiliser l'un des langages procéduraux tels que PL/pgSQL pas le proc SQL langage.
    Dans PL/pgSQL vous pouvez utiliser vars droit dans les instructions SQL.
    Pour un seul devis, vous pouvez utiliser la citation littérale de la fonction.

    • Il ne peut pas être fait dans postgres lui-même, mais il peut être fait dans le PSQL client de l'application.
    • plpgsql peut maintenant être utilisé dans postgres (depuis la version 9.0) )postgresql.org/docs/9.0/static/sql-do.html
    InformationsquelleAutor
  7. 9

    postgres (depuis la version 9.0) permet de blocs anonymes dans toutes les versions supportées de script côté serveur langues

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

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

    Que tout est à l'intérieur d'une chaîne de caractères externes de la chaîne de variables étant substitué auront besoin d'être échappés et ont cité deux fois. À l'aide dollar citant au lieu de cela ne donnera pas une protection complète contre les injections SQL.

    InformationsquelleAutor Jasen
  8. 5

    Une autre approche consiste à (ab)utiliser PostgreSQL GUC mécanisme pour créer des variables. Voir cet état de la réponse pour plus de détails et des exemples.

    Vous déclarez le GUC dans postgresql.conf, puis modifiez sa valeur au moment de l'exécution avec SET commandes et obtenez sa valeur avec current_setting(...).

    Je ne le recommande pas pour une utilisation générale, mais il pourrait être utile que dans de rares cas comme celui mentionné dans la question, où l'affiche voulait un moyen de fournir le niveau de l'application nom d'utilisateur à des déclencheurs et des fonctions.

    InformationsquelleAutor Craig Ringer
  9. 3

    Je l'ai résolu avec une table temp. 

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

    De cette façon, j'ai eu une "variable", je pourrais l'utiliser sur plusieurs requêtes, ce qui est unique pour la session. J'en avais besoin pour générer unique "noms d'utilisateur" bien que n'ayant pas encore les collisions en cas d'importation des utilisateurs avec le même nom d'utilisateur.

    • Cela semble être la seule façon de travailler à des outils visuels comme Heidi SQL.
    InformationsquelleAutor geon
  10. 2

    J'ai trouvé cette question et les réponses très utiles, mais aussi à confusion. J'ai eu beaucoup de mal à obtenir les variables citées au travail, voici donc la façon dont je l'ai eu de travail:

    \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 une instruction. Lorsque vous l'utilisez, apostrophes sera intégré dans la variable.

    REMARQUE! Quand je mets un commentaire après la cité de la variable il s'est aspiré dans le cadre de la variable lorsque j'ai essayé quelques 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.

    • \set deployment_pass 'string_password' ALTER USER :deployment_user WITH PASSWORD :'deployment_pass';
    • \ensemble n'est pas en SQL c'est un psql intégré dans la commande sql commentaires ne sont pas pris en charge.
    InformationsquelleAutor Nate
  11. 2

    Je manque vraiment de la fonctionnalité. Seul moyen de parvenir à quelque chose de semblable est d'utiliser des fonctions.

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

    • perl fonctions qui utilisent $_SHARED variable
    • stocker 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 de 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 = $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';

    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 table est aussi mauvais parce que vous avez à effacer ce est de temps en temps (et pas supprimer actuellement en train de travailler les variables de session)
    InformationsquelleAutor Kaiko Kaur
  12. 1

    Variables dans psql sucer. Si vous voulez déclarer un entier, vous devez saisir le nombre entier, puis faire un retour chariot, puis à la fin de l'instruction dans un point-virgule. Observer:

    Disons que je veux déclarer une variable de type entier my_var et de l'insérer dans une table test:

    Exemple table 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)

    Clairement, rien dans ce tableau encore:

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

    Nous déclarons une variable. Remarquez comment le point-virgule est sur la ligne suivante!

    thedatabase=# \set my_var 999
thedatabase=# ;

    Maintenant, nous pouvons insérer. Nous devons utiliser cette étrange ":''" à la recherche de la syntaxe:

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

    Il a travaillé!

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

    Explication:

    Alors... qu'advient-il si nous n'avons pas de point-virgule à la ligne suivante? La variable? Regardez:

    Nous déclarons my_var sans la nouvelle ligne.

    thedatabase=# \set my_var 999;

    Sélectionnez my_var.

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

    WTF ce que c'est? Ce n'est pas une entier, c'est un chaîne 999;!

    thedatabase=# select 999;
 ?column? 
----------
      999
(1 row)
    • La raison pour laquelle le point-virgule n'des choses inattendues pour vous est qu'un point-virgule qui termine une instruction SQL, mais vous êtes en train de saisir un psql commande \set, ce qui n'est pas SQL et ne prend PAS de point virgule. Mettre un point-virgule à la ligne suivante ne fera pas de mal, mais ne fait absolument rien. C'est une instruction vide.
    InformationsquelleAutor Display name
  13. 1

    J'ai posté une nouvelle solution pour ce sur un autre thread.

    Il utilise un tableau pour stocker des variables, et peuvent être mises à jour à tout moment. Statique, immuable fonction get est créé dynamiquement (par une autre fonction), déclenché par la mise à jour de votre table. Vous obtenez une belle table stockage, ainsi que des vitesses ultra rapides de immuable de lecture.

    InformationsquelleAutor Brev