¿Definir nombres de tablas y columnas como argumentos en una función plpgsql?

Resuelto fgcarto asked hace 11 años • 1 respuestas

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

Me gustaría crear una función que modifique una tabla y/o columna y no encuentro la forma 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 altere 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?

fgcarto avatar Aug 22 '13 22:08 fgcarto
Aceptado

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 del sistema o de entradas directas del usuario. De esta manera también evitas excepciones triviales con identificadores no estándar. Básicamente, existen tres métodos integrados:

1.format()

1ra consulta, desinfectada:

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

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

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

  • INSERTAR 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 varias columnas con un solo ALTER TABLEcomando es más económico.

2.regclass

También puede utilizar una conversión a una clase registrada ( regclass) para el caso especial de nombres de tablas existentes . Opcionalmente calificado por esquema. Esto falla de forma inmediata y elegante para los nombres de tablas que no son válidos ni visibles para el usuario que llama. La primera consulta desinfectada con un yeso para regclass:

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

Llamar:

SELECT foo('table_name');

O:

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

Aparte: considere usar just texten lugar devarchar(20) .

3.quote_ident()

La segunda consulta desinfectada:

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

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

Respuestas relacionadas:

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

¡Distingue mayúsculas y minúsculas!

Tenga en cuenta que los identificadores sin comillas no se escriben en minúsculas aquí. Cuando se usa como identificador en SQL [Postgres convierte a minúsculas automáticamente][7]. Pero aquí pasamos cadenas para SQL dinámico. Cuando se escapan como se demostró, los identificadores de mayúsculas y minúsculas (como UserS) se conservarán entre comillas dobles ( "UserS"), al igual que otros nombres no estándar como "name with space" "SELECT"etc. Por lo tanto, los nombres distinguen entre mayúsculas y minúsculas en este contexto.

Mi consejo permanente es utilizar exclusivamente identificadores legales en minúsculas y no preocuparse por eso.

Aparte: las comillas simples son para valores, las comillas dobles son para identificadores. Ver:

  • ¿Los nombres de las columnas de PostgreSQL distinguen entre mayúsculas y minúsculas?
Erwin Brandstetter avatar Aug 22 '2013 16:08 Erwin Brandstetter