function - query - dynamic where postgres

Nombre de tabla como parámetro de función PostgreSQL (6)

Quiero pasar un nombre de tabla como parámetro en una función de Postgres. Intenté este código:

CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer AS $$ BEGIN IF EXISTS (select * from quote_ident($1) where quote_ident($1).id=1) THEN return 1; END IF; return 0; END; $$ LANGUAGE plpgsql; select some_f(''table_name'');

Y obtuve esto:

ERROR: syntax error at or near "." LINE 4: ...elect * from quote_ident($1) where quote_ident($1).id=1)... ^ ********** Error ********** ERROR: syntax error at or near "."

Y aquí está el error que obtuve cuando cambié a esta select * from quote_ident($1) tab where tab.id=1 :

ERROR: column tab.id does not exist LINE 1: ...T EXISTS (select * from quote_ident($1) tab where tab.id...

Probablemente, quote_ident($1) funciona, porque sin el where quote_ident($1).id=1 parte obtengo 1 , lo que significa que algo está seleccionado. ¿Por qué puede funcionar el primer quote_ident($1) y el segundo no al mismo tiempo? ¿Y cómo podría resolverse esto?

Dentro del código plpgsql, la sentencia EXECUTE debe usarse para consultas en las que los nombres o columnas de las tablas provienen de variables. Además, el constructo IF EXISTS (<query>) no está permitido cuando la query se genera dinámicamente.

Aquí está su función con ambos problemas solucionados:

CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer AS $$ DECLARE v int; BEGIN EXECUTE ''select 1 FROM '' || quote_ident(param) || '' WHERE '' || quote_ident(param) || ''.id = 1'' INTO v; IF v THEN return 1; ELSE return 0; END IF; END; $$ LANGUAGE plpgsql;

El primero en realidad "no funciona" en el sentido en que te refieres, funciona solo en la medida en que no genera un error.

Pruebe SELECT * FROM quote_ident(''table_that_does_not_exist''); , y verá por qué su función devuelve 1: el select devuelve una tabla con una columna (llamada quote_ident ) con una fila (la variable $1 o en este caso particular table_that_does_not_exist ).

Lo que desea hacer requerirá SQL dinámico, que es en realidad el lugar donde las funciones quote_* están destinadas a ser utilizadas.

Esto se puede simplificar y mejorar aún más:

CREATE OR REPLACE FUNCTION some_f(_tbl regclass, OUT result integer) AS $func$ BEGIN EXECUTE format(''SELECT (EXISTS (SELECT 1 FROM %s WHERE id = 1))::int'', _tbl) INTO result; END $func$ LANGUAGE plpgsql;

Llamada (ejemplo con nombre calificado por esquema - ver a continuación):

SELECT some_f(''myschema.mytable''); -- would fail with quote_ident()

SELECT some_f(''"my very uncommon table name"'')

Puntos principales

Use un parámetro OUT para simplificar la función. Puede seleccionar directamente el resultado del SQL dinámico y terminarlo. No hay necesidad de variables adicionales y código.
EXISTS hace exactamente lo que quieres. Se true si la fila existe o es false caso contrario. Hay varias formas de hacer esto, EXISTS es típicamente el más eficiente.
Parece que quieres un número entero , así que boolean resultado boolean de EXISTS a integer , lo que da exactamente lo que tienes. Yo devolvería boolean lugar.
Utilizo el tipo de identificador de objeto regclass como tipo de entrada para _tbl . Eso hace todo lo que quote_ident(_tbl) o format(''%I'', _tbl) haría, pero mejor, porque:
- ... también evita la inyección de SQL .
- .. falla inmediatamente y con mayor gracia si el nombre de la tabla no es válido / no existe / es invisible para el usuario actual. (Un parámetro de regclass solo se aplica a las tablas existentes ).
- ... funciona con nombres de tabla calificados por esquema, donde un quote_ident(_tbl) simple quote_ident(_tbl) o un format(%I) fallaría porque no pueden resolver la ambigüedad. Tendría que pasar y escapar los nombres de esquema y tabla por separado.
Todavía uso el format() , porque simplifica la sintaxis (y para demostrar cómo se usa), pero con %s lugar de %I Normalmente, las consultas son más complejas, por lo que format() ayuda más. Para el ejemplo simple, también podríamos concatenar:
EXECUTE ''SELECT (EXISTS (SELECT 1 FROM '' || _tbl || '' WHERE id = 1))::int''
No es necesario calificar la tabla de la columna de id solo hay una sola tabla en la lista FROM . No hay ambigüedad posible en este ejemplo. Los comandos de SQL (dinámicos) dentro de EXECUTE tienen un alcance separado , las variables de función o los parámetros no son visibles allí, a diferencia de los comandos de SQL simple en el cuerpo de la función.

Probado con PostgreSQL 9.1. format() requiere al menos esa versión.

He aquí por qué siempre escapa de la entrada del usuario para el SQL dinámico correctamente:

SQL Fiddle que demuestra la inyección de SQL

No hagas esto

Esa es la respuesta. Es un terrible anti patrón. ¿Para qué sirve? Si el cliente conoce la tabla de la que quiere datos, ¡ SELECT FROM ThatTable ! Si ha diseñado su base de datos de una manera que sea necesaria, probablemente la haya diseñado mal. Si su capa de acceso a datos necesita saber si existe un valor en una tabla, es trivialmente fácil hacer la parte de SQL dinámico en ese código. Insertarlo en la base de datos no es bueno.

Tengo una idea: instalemos un dispositivo dentro de los elevadores donde puede escribir el número del piso que desea. Luego, cuando presiona "Ir", mueve una manecilla mecánica al botón correcto para el piso deseado y lo presiona. ¡Revolucionario!

Aparentemente, mi respuesta fue muy breve para explicar, así que estoy reparando este defecto con más detalle.

No tenía intención de burla. Mi ejemplo tonto de ascensor fue el mejor dispositivo que pude imaginar para señalar sucintamente los defectos de la técnica sugerida en la pregunta. Esa técnica agrega una capa completamente innecesaria de indirección, y mueve innecesariamente la elección del nombre de la tabla de un espacio de llamante utilizando un DSL (SQL) robusto y bien entendido en un híbrido usando código SQL oscuro / extraño del lado del servidor.

Dicha responsabilidad dividida a través del movimiento de la lógica de construcción de consultas en SQL dinámico hace que el código sea más difícil de entender. Destruye una convención perfectamente razonable (cómo una consulta SQL elige qué seleccionar) en el nombre del código personalizado lleno de posibilidades de error.

El SQL dinámico ofrece la posibilidad de inyección SQL que es difícil de reconocer en el código del front-end o en el código del back-end por separado (uno debe inspeccionarlos juntos para ver esto).
Los procedimientos y funciones almacenados pueden acceder a los recursos que el propietario del SP / función tiene derechos, pero la persona que llama no tiene acceso. Por lo que entiendo, cuando usa código que produce SQL dinámico y lo ejecuta, la base de datos ejecuta el SQL dinámico bajo los derechos de la persona que llama. Esto significa que no podrá usar objetos privilegiados en absoluto, o tiene que abrirlos a todos los clientes, aumentando el área de posible ataque a datos privilegiados. Establecer el SP / función en el momento de la creación para que siempre se ejecute como un usuario particular (en SQL Server, EXECUTE AS ) puede resolver ese problema, pero hace las cosas más complicadas. Esto agrava el riesgo de inyección de SQL mencionado en el punto anterior, al hacer que el SQL dinámico sea un vector de ataque muy tentador.
Cuando un desarrollador debe entender qué está haciendo el código de la aplicación para modificarlo o corregir un error, le resultará muy difícil obtener la consulta SQL exacta que se está ejecutando. Se puede usar el generador de perfiles SQL, pero esto requiere privilegios especiales y puede tener efectos de rendimiento negativos en los sistemas de producción. El SP puede registrar la consulta ejecutada, pero esto aumenta la complejidad sin ningún motivo (mantener tablas nuevas, depurar datos antiguos, etc.) y no es totalmente obvio. De hecho, algunas aplicaciones tienen una arquitectura tal que el desarrollador no tiene credenciales de base de datos, por lo que le resulta casi imposible ver la consulta que se envía.
Cuando se produce un error, como cuando intenta seleccionar una tabla que no existe, recibirá un mensaje en la línea de "nombre de objeto no válido" de la base de datos. Eso ocurrirá exactamente igual si está redactando el SQL en la parte de atrás o en la base de datos, pero la diferencia es que un desarrollador deficiente que está tratando de resolver el problema del sistema tiene que adentrarse un nivel más profundo en otra cueva por debajo de la que el el problema realmente existe, profundizar en el procedimiento de maravilla que lo hace todo e intentar descubrir cuál es el problema. Los registros no mostrarán "Error en GetWidget", se mostrará "Error in OneProcedureToRuleThemAllRunner". Esta abstracción solo empeorará tu sistema.

Este es un ejemplo mucho mejor en pseudo-C # de nombres de tablas de conmutación basados en un parámetro:

string sql = string.Format("SELECT * FROM {0};", escapeSqlIdentifier(tableName)); results = connection.Execute(sql);

Cada defecto que he mencionado con la otra técnica está completamente ausente de este ejemplo.

No hay ningún propósito, ningún beneficio, ninguna mejora posible al enviar un nombre de tabla a un procedimiento almacenado.

Si la pregunta era probar si la tabla está vacía o no (id = 1), aquí hay una versión simplificada del proceso almacenado de Erwin:

CREATE OR REPLACE FUNCTION isEmpty(tableName text, OUT zeroIfEmpty integer) AS $func$ BEGIN EXECUTE format(''SELECT COALESCE ((SELECT 1 FROM %s LIMIT 1),0)'', tableName) INTO zeroIfEmpty; END $func$ LANGUAGE plpgsql;

Tengo la versión 9.4 de PostgreSQL y siempre uso este código:

CREATE FUNCTION add_new_table(text) RETURNS void AS $BODY$ begin execute ''CREATE TABLE '' || $1 || ''( item_1 type, item_2 type )''; end; $BODY$ LANGUAGE plpgsql

Y entonces:

SELECT add_new_table(''my_table_name'');

Funciona bien para mí.

¡Atención! El ejemplo anterior es uno de los que muestra "¿Cómo es posible si queremos mantener la seguridad durante la consulta de la base de datos?": P