uso - ¿Cómo se usan las variables de script en PostgreSQL?

uso de variables en postgresql

Question

En MS SQL Server, creo mis scripts para usar variables personalizables:

DECLARE @somevariable int  
SELECT @somevariable = -1

INSERT INTO foo VALUES ( @somevariable )

Luego cambiaré el valor de @somevariable en tiempo de ejecución, según el valor que desee en la situación particular. Dado que está en la parte superior del guión, es fácil de ver y recordar.

¿Cómo hago lo mismo con PostgreSQL?

Google buscó las variables de PSQL , pero está implícito que solo se pueden usar en otros comandos de barra , no en SQL real.

EDIT: encontré mis propias respuestas, y en realidad son bastante complicadas. Ordena las publicaciones más antiguas-> más nuevas para seguir mis descubrimientos.

Encontré mi propia respuesta más abajo en esa página vinculada:

Una característica útil adicional de las variables psql es que puede sustituirlas ("interpolarlas") en sentencias de SQL regulares.

Intenté esto y tuve un problema, pero esto sugiere que mi problema no está relacionado con la variable después de todo.

Answer 1

Encontré esta pregunta y las respuestas extremadamente útiles, pero también confusas. Tuve muchos problemas para que funcionaran las variables citadas, así que esta es la forma en que lo hice funcionar:

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

De esta forma puedes definir la variable en una declaración. Cuando lo use, las comillas simples se incorporarán a la variable.

¡NOTA! Cuando puse un comentario después de la variable citada, me absorbió como parte de la variable cuando probé algunos de los métodos en otras respuestas. Eso realmente me estaba aturdiendo por un tiempo. Con este método, los comentarios parecen tratarse como cabría esperar.

Answer 2

Específicamente para psql , también puede pasar variables psql desde la línea de comando; puedes pasarlos con -v . Aquí hay un ejemplo de uso:

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

Tenga en cuenta que el signo de dos puntos no está citado, y luego se cita el nombre de la variable. Extraña sintaxis, lo sé. Esto solo funciona en psql; no funcionará en (digamos) PgAdmin-III.

Esta sustitución ocurre durante el procesamiento de entrada en psql, por lo que no puede (por ejemplo) definir una función que use :''filepath'' y esperar que el valor de :''filepath'' cambie de sesión a sesión. Será sustituido una vez, cuando la función esté definida, y luego será una constante después de eso. Es útil para secuencias de comandos, pero no para el uso en tiempo de ejecución.

Answer 3

FWIW, el problema real era que había incluido un punto y coma al final de mi comando / set:

/ set owner_password ''thepassword'';

El punto y coma se interpretó como un personaje real en la variable:

/ echo: owner_password thepassword;

Entonces cuando intenté usarlo:

CREATE ROLE myrole INICIE SESIÓN CONTRASEÑA NOCRITURA: owner_password NOINHERIT CREATEDB CREATEROLE VÁLIDO HASTA "infinito";

...Tengo esto:

CREATE ROLE myrole INICIE SESIÓN CONTRASEÑA NOCRITURA thepassword; NOINHERIT CREATEDB CREATEROLE VÁLIDO HASTA ''infinito'';

Eso no solo no logró establecer las comillas alrededor del literal, sino que dividió el comando en 2 partes (el segundo de los cuales no era válido ya que comenzó con "NOINHERIT").

La moraleja de esta historia: las "variables" de PostgreSQL son realmente macros utilizadas en la expansión de texto, no en valores verdaderos. Estoy seguro de que es útil, pero al principio es complicado.

Answer 4

Las variables de Postgres se crean mediante el comando / set, por ejemplo ...

/set myvariable value

... y luego puede ser sustituido, por ejemplo, como ...

SELECT * FROM :myvariable.table1;

... o ...

SELECT * FROM table1 WHERE :myvariable IS NULL;

... pero, si desea usar la variable como el valor en una consulta de cadena condicional, como ...

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

... entonces necesita incluir las comillas en la variable en sí, ya que lo anterior no funcionará. En su lugar, defina su variable como tal ...

/set myvariable ''value''

Sin embargo, si, como yo, te encontraste en una situación en la que querías hacer una cadena a partir de una variable existente, el truco fue que ...

/set quoted_myvariable ''/''' :myvariable ''/'''

¡Ahora tiene una variable entre comillas y sin comillas de la misma cadena! Y puedes hacer algo como esto ...

INSERT INTO :myvariable.table1 SELECT * FROM table2 WHERE column1 = :quoted_myvariable;

Answer 5

Lo resolví con una tabla temporal.

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

De esta forma, tuve una "variable" que podría usar en múltiples consultas, que es única para la sesión. Lo necesitaba para generar "nombres de usuario" únicos y no tener colisiones si importo usuarios con el mismo nombre de usuario.

Answer 6

Necesita utilizar uno de los lenguajes de procedimiento como PL / pgSQL, no el lenguaje de proceso de SQL. En PL / pgSQL puede usar vars directamente en sentencias de SQL. Para comillas simples puede usar la función literal de cita.

Answer 7

Otro enfoque es (ab) utilizar el mecanismo PostgreSQL GUC para crear variables. Vea esta respuesta previa para detalles y ejemplos.

Usted declara el GUC en postgresql.conf , luego cambia su valor en tiempo de ejecución con los comandos SET y obtiene su valor con current_setting(...) .

No lo recomiendo para uso general, pero podría ser útil en casos limitados como el mencionado en la pregunta vinculada, donde el póster quería una forma de proporcionar el nombre de usuario de la aplicación a los desencadenantes y funciones.

Answer 8

Puede intentar usar una cláusula 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;

Answer 9

Realmente echo de menos esa característica. La única forma de lograr algo similar es usar funciones.

Lo he usado de dos maneras:

• funciones perl que usan la variable $ _SHARED
• almacena tus variables en la tabla

Versión 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;

Versión de la tabla:

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

Notas:

• plperlu es más rápido que perl
• pg_backend_pid no es la mejor identificación de sesión, considere usar pid combinado con backend_start desde pg_stat_activity
• esta versión de la tabla también es mala porque tienes que borrar esto de vez en cuando (y no eliminar las variables de sesión que están trabajando actualmente)

Answer 10

Una palabra final sobre las variables de PSQL:

1. No se expanden si los incluye entre comillas simples en la declaración de SQL. Por lo tanto, esto no funciona:

   SELECT * FROM foo WHERE bar = '':myvariable''

2. Para expandir a un literal de cadena en una declaración de SQL, debe incluir las comillas en el conjunto de variables. Sin embargo, el valor de la variable ya debe estar entre comillas, lo que significa que necesita un segundo conjunto de comillas, y el conjunto interno debe ser escapado. Por lo tanto, necesitas:

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

   EDITAR : comenzando con PostgreSQL 9.1, puede escribir en su lugar:

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

Answer 11

postgres (desde la versión 9.0) permite bloques anónimos en cualquiera de los lenguajes de scripting del lado del servidor compatibles

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

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

Como todo está dentro de una cadena, las variables de cadena externas que se sustituyan deberán escaparse y citarse dos veces. Usar cotizaciones en dólares en cambio no brindará protección total contra la inyección de SQL.