Refactorice una función PL / pgSQL para devolver el resultado de varias consultas SELECT

Por fin luego de mucho trabajar pudimos hallar la solución de este atasco que algunos los usuarios de este sitio web presentan. Si quieres aportar algún dato puedes aportar tu conocimiento.

Solución:

SQL dinámico y RETURN escribe

^{(Dejé lo mejor para el final, ¡sigue leyendo!)}

Quieres ejecutar SQL dinámico. En principio, eso es simple en plpgsql con la ayuda de EXECUTE. Tu no necesitar un cursor; de hecho, la mayoría de las veces está mejor sin cursores explícitos.
Encuentre ejemplos sobre SO con una búsqueda.

El problema con el que te encuentras: quieres devolver registros de tipo aún no definido. Una función necesita declarar el tipo de retorno con el RETURNS cláusula (o con OUT o INOUT parámetros). En su caso, tendría que recurrir a registros anónimos, porque número, nombres y tipos de las columnas devueltas varían. Igual que:

CREATE FUNCTION data_of(integer)
  RETURNS SETOF record AS ...

Sin embargo, esto no es particularmente útil. De esta manera, tendría que proporcionar una lista de definiciones de columna con cada llamada de la función. Igual que:

SELECT * FROM data_of(17)
AS foo (colum_name1 integer
      , colum_name2 text
      , colum_name3 real);

Pero, ¿cómo harías esto si no conoces las columnas de antemano?
Podría recurrir a tipos de datos de documentos menos estructurados como json, jsonb, hstore o xml:

• ¿Cómo almacenar una tabla de datos en una base de datos?

Pero para el propósito de esta pregunta, supongamos que desea devolver columnas individuales, correctamente escritas y nombradas tanto como sea posible.

Solución simple con tipo de retorno fijo

La columna datahora parece ser un hecho, asumiré el tipo de datos timestamp y que siempre hay dos columnas más con diferentes nombres y tipos de datos.

Nombres abandonaremos en favor de nombres genéricos en el tipo de retorno.
Tipos abandonaremos, también, y echaremos todo a text ya que cada el tipo de datos se puede convertir a text.

CREATE OR REPLACE FUNCTION data_of(_id integer)
  RETURNS TABLE (datahora timestamp, col2 text, col3 text)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _sensors text := 'col1::text, col2::text';  -- cast each col to text
   _type    text := 'foo';
BEGIN
   RETURN QUERY EXECUTE '
      SELECT datahora, ' || _sensors || '
      FROM   ' || quote_ident(_type) || '
      WHERE  id = $1
      ORDER  BY datahora'
   USING  _id;

END
$func$;

¿Como funciona esto?

• Las variables _sensors y _type en su lugar, podrían ser parámetros de entrada.

• Nota la RETURNS TABLE cláusula.

• Tenga en cuenta el uso de RETURN QUERY EXECUTE. Esa es una de las formas más elegantes de devolver filas de una consulta dinámica.

• Utilizo un nombre para el parámetro de la función, solo para hacer el USING cláusula de RETURN QUERY EXECUTE menos confuso. $1 en el SQL-string no se refiere al parámetro de la función sino al valor pasado con el USING cláusula. (Ambos resultan ser $1 en su respectivo alcance en este sencillo ejemplo.)

• Tenga en cuenta el valor de ejemplo para _sensors: cada columna se convierte para escribir text.

• Este tipo de código es muy vulnerable a inyección SQL. yo suelo quote_ident() para protegerse contra él. Agrupar un par de nombres de columna en la variable _sensors previene el uso de quote_ident() (¡y suele ser una mala idea!). Asegúrese de que no haya cosas malas allí de otra manera, por ejemplo, ejecutando individualmente los nombres de las columnas a través de quote_ident() en lugar de. A VARIADIC el parámetro viene a la mente …

Más simple con PostgreSQL 9.1+

Con la versión 9.1 o posterior puede utilizar format() para simplificar aún más:

RETURN QUERY EXECUTE format('
   SELECT datahora, %s  -- identifier passed as unescaped string
   FROM   %I            -- assuming the name is provided by user
   WHERE  id = $1
   ORDER  BY datahora'
  ,_sensors, _type)
USING  _id;

Nuevamente, los nombres de las columnas individuales podrían escaparse correctamente y serían la forma limpia.

Número variable de columnas que comparten el mismo tipo

Después de que se actualice su pregunta, parece que su tipo de devolución tiene

• una variable número de columnas
• pero todas las columnas de la misma escribedouble precision (alias float8)

Como tenemos que definir el RETURN tipo de función a la que recurro ARRAY escriba en este caso, que puede contener un número variable de valores. Además, devuelvo un array con nombres de columna, por lo que también podría analizar los nombres del resultado:

CREATE OR REPLACE FUNCTION data_of(_id integer)
  RETURNS TABLE (datahora timestamp, names text[], values float8[] ) AS
$func$
DECLARE
   _sensors text := 'col1, col2, col3';  -- plain list of column names
   _type    text := 'foo';
BEGIN
   RETURN QUERY EXECUTE format('
      SELECT datahora
           , string_to_array($1)  -- AS names
           , ARRAY[%s]            -- AS values
      FROM   %s
      WHERE  id = $2
      ORDER  BY datahora'
    , _sensors, _type)
   USING  _sensors, _id;
END
$func$  LANGUAGE plpgsql;

Varios tipos de mesas completas

Si realmente está tratando de regresar todas las columnas de una tabla (por ejemplo, una de las tablas en la página vinculada, luego use esta solución simple y muy poderosa con una tipo polimórfico:

CREATE OR REPLACE FUNCTION data_of(_tbl_type anyelement, _id int)
  RETURNS SETOF anyelement AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('
      SELECT *
      FROM   %s  -- pg_typeof returns regtype, quoted automatically
      WHERE  id = $1
      ORDER  BY datahora'
    , pg_typeof(_tbl_type))
   USING  _id;
END
$func$ LANGUAGE plpgsql;

Llamar (¡importante!):

SELECT * FROM data_of(NULL::pcdmet, 17);

Reemplazar pcdmet en la llamada con cualquier otro nombre de tabla.

¿Como funciona esto?

• anyelement es un pseudo tipo de datos, un tipo polimórfico, un marcador de posición para cualquierarray tipo de datos. Todas las apariciones de anyelement en la función evaluar al mismo tipo proporcionado en tiempo de ejecución. Al proporcionar un valor de un tipo definido como argumento a la función, definimos implícitamente el tipo de retorno.

• PostgreSQL define automáticamente un tipo de fila (un tipo de datos compuestos) para cada tabla creada, por lo que hay un tipo bien definido para cada tabla. Esto incluye tablas temporales, que es conveniente para un uso ad-hoc.

• Cualquier tipo puede ser NULL. Entonces entregamos un NULL valor, emitido al tipo de tabla: NULL::pcdmet.

• Ahora la función devuelve un tipo de fila bien definido y podemos usar SELECT * FROM data_of(...) para descomponer la fila y obtener columnas individuales.

• pg_typeof(_tbl_type) devuelve el nombre de la tabla como tipo de identificador de objeto regtype. Cuando se convierte automáticamente a text, los identificadores son automáticamente entre comillas dobles y calificadas por esquema si es necesario. Por lo tanto, la inyección de SQL no es posible. Esto incluso puede tratar con nombres de tabla calificados por esquema donde quote_ident() fallaría.

Probablemente querrás devolver un cursor. Prueba algo como esto (no lo he probado):

CREATE OR REPLACE FUNCTION data_of(integer)
  RETURNS refcursor AS
$BODY$
DECLARE
      --Declaring variables
      ref refcursor;
BEGIN
      -- make sure `sensors`, `type`, $1 variable has valid value
      OPEN ref FOR 'SELECT Datahora,' || sensors ||
      ' FROM ' || type ||
      ' WHERE nomepcd=' || $1 ||' ORDER BY Datahora;';
      RETURN ref;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION data_of(integer) OWNER TO postgres;

Sección de Reseñas y Valoraciones

Nos puedes apoyar nuestra tarea escribiendo un comentario y dejando una puntuación te damos la bienvenida.

---

---

Utiliza Nuestro Buscador