¿Definir nombres de tabla y columna como argumentos en una función plpgsql?

Debe ser simple, pero estoy dando mis primeros pasos en las funciones de Postgres y no puedo encontrar nada que funcione …

Me gustaría crear una función que modifique una tabla y / o columna y no puedo encontrar la manera correcta de especificar mis tablas y columnas como argumentos en mi función.

Algo como:

CREATE OR REPLACE FUNCTION foo(t table) RETURNS void AS $$ BEGIN alter table t add column c1 varchar(20); alter table t add column c2 varchar(20); alter table t add column c3 varchar(20); alter table t add column c4 varchar(20); END; $$ LANGUAGE PLPGSQL; select foo(some_table) 

En otro caso, me gustaría tener una función que modifique una determinada columna de una determinada tabla:

 CREATE OR REPLACE FUNCTION foo(t table, c column) RETURNS void AS $$ BEGIN UPDATE t SET c = "This is a test"; END; $$ LANGUAGE PLPGSQL; 

¿Es posible hacer eso?

Debe defenderse contra la inyección de SQL cada vez que convierte la entrada del usuario en código. Eso incluye nombres de tablas y columnas provenientes de catálogos de sistemas o de la entrada directa del usuario. De esta forma, también evita excepciones triviales con identificadores no estándar. Básicamente, hay tres métodos integrados:

1. format()

Primera consulta, desinfectada:

 CREATE OR REPLACE FUNCTION foo(_t text) RETURNS void AS $func$ BEGIN EXECUTE format(' ALTER TABLE %I ADD COLUMN c1 varchar(20) , ADD COLUMN c2 varchar(20)', _t); END $func$ LANGUAGE plpgsql; 

format() requiere Postgres 9.1 o posterior. Úselo con el especificador de formato %I

El nombre de la tabla solo puede ser ambiguo. Es posible que deba proporcionar el nombre del esquema para evitar cambiar la tabla incorrecta por accidente. Relacionado:

  • INSERT con el nombre de la tabla dinámica en la función de activación
  • ¿Cómo influye search_path en la resolución del identificador y el “esquema actual”?

Aparte: agregar columnas múltiples con un solo comando ALTER TABLE es más barato.

2. regclass

También puede usar un cast a una clase registrada ( regclass ) para el caso especial de nombres de tablas existentes . Opcionalmente calificado por esquema. Esto falla inmediatamente y con gracia para los nombres de tabla que no son válidos y visibles para el usuario que llama. La primera consulta desinfectada con un elenco para regclass :

 CREATE OR REPLACE FUNCTION foo(_t regclass) RETURNS void AS $func$ BEGIN EXECUTE 'ALTER TABLE '|| _t ||' ADD COLUMN c1 varchar(20) , ADD COLUMN c2 varchar(20)'; END $func$ LANGUAGE plpgsql; 

Llamada:

 SELECT foo('table_name'); 

O:

 SELECT foo('my_schema.table_name'::regclass); 

Aparte: considere usar solo text lugar de varchar(20) .

3. quote_ident()

La segunda consulta desinfectada:

 CREATE OR REPLACE FUNCTION foo(_t regclass, _c text) RETURNS void AS $func$ BEGIN EXECUTE 'UPDATE '|| _t ||' -- sanitized with regclass SET '|| quote_ident(_c) ||' = ''This is a test'''; END $func$ LANGUAGE plpgsql; 

Para múltiples concatenaciones / interpolaciones, format() es más limpio …

Respuestas relacionadas:

  • Nombre de tabla como parámetro de función PostgreSQL
  • Funciones de Postgres vs consultas preparadas

¡Distingue mayúsculas y minúsculas!

Tenga en cuenta que los identificadores sin comillas no se envían a minúsculas aquí. Cuando se utiliza como identificador en SQL, Postgres envía a minúsculas automáticamente . Pero aquí pasamos cadenas para SQL dynamic. Cuando se escapa como se demostró, los identificadores de CaMel-Case (como UserS ) se conservarán mediante doublequoting ( "UserS" ), al igual que otros nombres no estándar como "name with space" "SELECT" etc. Por lo tanto, los nombres son sensibles a mayúsculas y minúsculas en este contexto.

Mi consejo permanente es utilizar identificadores minúsculos legales exclusivamente y nunca preocuparse por eso.

A un lado: comillas simples para valores, comillas dobles para identificadores .