Comment utiliser le script de variables dans PostgreSQL?

Question

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 lors 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 puis-je faire de même avec PostgreSQL?

Googler tourné PSQL variables, mais il est implicite qu'ils ne peuvent être utilisés dans d'autres commandes slash, non pas dans SQL.

EDIT: Trouvé mes propres réponses, et ils sont en fait assez complexe. Trier les posts plus-la>version plus récente pour suivre mes découvertes.

---

Trouvé ma réponse plus bas que la page liée:

Un supplément de fonctionnalité utile de psql variables est que vous pouvez remplacer ("interpoler") dans les instructions SQL.

J'ai essayé ce déjà eu un problème, mais cela donne à penser que mon problème n'est pas lié à la variable après tout.

Answer 1

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

\set myvariable value

... et peut être remplacé, par exemple, que ...

SELECT * FROM :myvariable.table1;

... ou ...

SELECT * FROM table1 WHERE :myvariable IS NULL;

... mais, si vous voulez utiliser la variable comme la valeur d'un conditionnel chaîne de requête, comme la ...

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;

Answer 2

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 devez:

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

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

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

Answer 3

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)

Notez 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; cela ne fonctionnera 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 variation à partir d'une 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.

Answer 4

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 de la variable:

\echo :owner_password thepassword;

Alors, 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.