SELECT INTO dinámico en la función PL/pgSQL

El paso a paso o código que verás en este artículo es la resolución más eficiente y válida que encontramos a esta duda o problema.

Solución:

CREATE OR REPLACE FUNCTION myfunc(_tbl_pattern text, _schema text = 'public')
  RETURNS void AS  -- or whatever you want to return
$func$
DECLARE
   _tb_name information_schema.tables.table_name%TYPE;  -- currently varchar
   _tc      bigint;  -- count() returns bigint
BEGIN
   FOR _tb_name IN
      SELECT table_name
      FROM   information_schema.tables
      WHERE  table_schema = _schema
      AND    table_name   ~ _tbl_pattern  -- see below!
   LOOP
      EXECUTE format('SELECT count(*) FROM %I.%I', _schema, _tb_name)
      INTO _tc;      

      -- do something with _tc
   END LOOP;
END
$func$  LANGUAGE plpgsql;

notas

• Antepuse todos los parámetros y variables con un guión bajo (_) para evitar nombrando colisiones con las columnas de la tabla. Sólo una convención útil.

• _tc debiera ser bigintya que eso es lo que la función agregada count() devoluciones.

• El tipo de datos de _tb_name se deriva de su columna principal dinámicamente: information_schema.tables.table_name%TYPE. Ver el capítulo Tipos de copia en el manual

• ¿Estás seguro de que solo quieres las tablas enumeradas en information_schema.tables? Tiene sentido, pero tenga en cuenta las implicaciones. Ver:

  • Cómo verificar si existe una tabla en un esquema dado

• a_horse ya señaló el manual y Andy proporcionó un ejemplo de código. Así es como tú asignar una sola fila o valor devuelto de una consulta dinámica con EXECUTE a una variable (fila). A único columna (como count en el ejemplo) se descompone del tipo de fila automáticamente, por lo que podemos asignar a la variable escalar tc directamente, de la misma manera que asignaríamos una fila completa a un registro o variable de fila. Relacionado:

  • Cómo obtener el valor de un nombre de campo generado dinámicamente en PL/pgSQL

• Calificación de esquema el nombre de la tabla en la consulta dinámica. Puede haber otras tablas con el mismo nombre en el actual search_path, lo que daría como resultado resultados completamente erróneos (¡y muy confusos!) sin calificación de esquema. ¡Bicho furtivo! O este el esquema no esta en el search_path en absoluto, lo que haría que la función generara una excepción inmediatamente.

  • ¿Cómo influye search_path en la resolución del identificador y el “esquema actual”?

• Siempreidentificadores de cotizaciones adecuadamente para defenderse contra la inyección SQL y los errores aleatorios. El esquema y la tabla deben citarse por separado! Ver:

  • Nombre de tabla como parámetro de función de PostgreSQL
  • Truncar todas las tablas en una base de datos de Postgres

• Yo uso el operador de expresión regular ~ en table_name ~ _tbl_pattern en vez de table_name LIKE ('%' || _tbl_pattern || '%'), eso es más simple. ¡Tenga cuidado con los caracteres especiales en el parámetro de patrón de cualquier manera! Ver:

  • LIKE inverso de PostgreSQL
  • Función de escape para expresiones regulares o patrones LIKE
  • Coincidencia de patrones con LIKE, SIMILAR TO o expresiones regulares en PostgreSQL

• Establecí un valor predeterminado para el nombre del esquema en la llamada a la función: _schema text = 'public'. Solo por conveniencia, puede o no querer eso. Ver:

  • Asignación de valor predeterminado para el tipo

---

Dirigiendo tu comentario: para pasar valoresutilizar el USING cláusula como:

EXECUTE format('SELECT count(*) FROM %I.%I
                WHERE some_column = $1', _schema, _tb_name,column_name)
USING user_def_variable;

Relacionado:

• INSERTAR con nombre de tabla dinámica en la función de activación

Reseñas y valoraciones

---

---

Utiliza Nuestro Buscador