sql - salida - retornar cursor procedimiento almacenado oracle

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

Question

Escribí una función que genera una consulta de PostgreSQL SELECT bien formada en forma de texto. Ahora ya no quiero dar salida a un texto, sino que realmente ejecuto la declaración SELECT generada contra la base de datos y devuelvo el resultado, tal como lo haría la consulta en sí misma.

Lo que tengo hasta ahora

CREATE OR REPLACE FUNCTION data_of(integer)
  RETURNS text AS
$BODY$
DECLARE
   sensors varchar(100);   -- holds list of column names
   type    varchar(100);   -- holds name of table
   result  text;           -- holds SQL query
       -- declare more variables

BEGIN
      -- do some crazy stuff

      result := ''SELECT/r/nDatahora,'' || sensors ||
      ''/r/n/r/nFROM/r/n'' || type ||
      ''/r/n/r/nWHERE/r/id='' || $1 ||''/r/n/r/nORDER BY Datahora;'';

      RETURN result;
END;
$BODY$
LANGUAGE ''plpgsql'' VOLATILE;
ALTER FUNCTION data_of(integer) OWNER TO postgres;

sensors contiene la lista de nombres de columnas para el type tabla. Esos son declarados y completados en el curso de la función. Eventualmente, tienen valores como:

• sensors : ''column1, column2, column3''
  Excepto por Datahora ( timestamp ), todas las columnas son de tipo double precision .

• type : ''myTable''
  Puede ser el nombre de una de las cuatro tablas. Cada uno tiene columnas diferentes, a excepción de la columna común Datahora .

Definición de las tablas subyacentes .

Los sensors variables mantendrán todas las columnas que se muestran aquí para la tabla correspondiente en type . Por ejemplo: si el type es pcdmet , los sensors serán ''datahora,dirvento,precipitacao,pressaoatm,radsolacum,tempar,umidrel,velvento''

Las variables se usan para construir una instrucción SELECT que se almacena en el result . Me gusta:

SELECT Datahora, column1, column2, column3
FROM   myTable
WHERE  id=20
ORDER  BY Datahora;

En este momento, mi función devuelve esta declaración como text . Copio y pego y lo ejecuto en pgAdmin o vía psql. Quiero automatizar esto, ejecutar la consulta automáticamente y devolver el resultado. ¿Cómo puedo hacer eso?

Answer 1

SQL dinámico y tipo RETURN

^{(¡Guardé lo mejor para el final, sigo leyendo!)}
Desea ejecutar SQL dinámico . En principio, eso es simple en plpgsql con la ayuda de EXECUTE . No necesita un cursor, de hecho, la mayoría de las veces se encuentra mejor sin los cursores explícitos.
Encuentre ejemplos en SO con una búsqueda .

El problema con el que se encuentra: desea devolver registros de tipo aún no definido . Una función necesita declarar el tipo de devolución con la cláusula RETURNS (o con los parámetros OUT o INOUT ). En su caso, debería recurrir a registros anónimos, ya que el número , los nombres y los tipos de columnas devueltas varían. Me gusta:

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

Sin embargo, esto no es particularmente útil. De esta forma, deberá proporcionar una lista de definición de columna con cada llamada de la función. Me gusta:

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

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

• ¿Cómo almacenar una tabla de datos (o Lista <KeyValuePair <int, Object >> o Dictionary) en la base de datos?

Pero a los fines de esta pregunta, supongamos que desea devolver columnas individuales, correctamente tipadas y con nombre tanto como sea posible.

Solución simple con tipo de retorno fijo

La columna datahora parece estar dada, supongo que la fecha y timestamp tipo de datos y que siempre hay dos columnas más con nombres y tipos de datos variables.

Nombres que abandonaremos a favor de nombres genéricos en el tipo de devolución.
Tipos que también abandonaremos y enviaremos todo al text ya que cada tipo de datos puede convertirse en text .

CREATE OR REPLACE FUNCTION data_of(_id integer)
  RETURNS TABLE (datahora timestamp, col2 text, col3 text) 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$ LANGUAGE plpgsql;

¿Como funciona esto?

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

• Tenga en cuenta la cláusula RETURNS .

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

• Utilizo un nombre para el parámetro de función, solo para hacer que la cláusula USING de RETURN QUERY EXECUTE menos confusa. $1 en la cadena SQL no hace referencia al parámetro de la función sino al valor pasado con la cláusula USING . (Ambos resultan ser $1 en sus respectivos ámbitos en este sencillo ejemplo).

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

• Este tipo de código es muy vulnerable a la inyección de SQL . Uso quote_ident() para protegerme de ello. Unir juntos un par de nombres de columna en la variable _sensors previene el uso de quote_ident() (¡y típicamente es una mala idea!). Asegúrese de que no haya problemas de otra manera, por ejemplo ejecutando los nombres de las columnas a través de quote_ident() . Un parámetro VARIADIC viene a la mente ...

Más simple con PostgreSQL 9.1+

Con la versión 9.1 o posterior, puede usar quote_ident() 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;

Una vez más, los nombres de las columnas individuales podrían escaparse correctamente y sería la manera más limpia.

Número variable de columnas que comparten el mismo tipo

Después de las actualizaciones de su pregunta, parece que su tipo de devolución tiene

• un número variable de columnas
• pero todas las columnas del mismo tipo tienen double precision (alias float8 )

Como tenemos que definir el tipo RETURN de una función, recurro a un tipo ARRAY en este caso, que puede contener un número variable de valores. Además, devuelvo una matriz con nombres de columna, por lo que también puedes 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 tablas completas

Si realmente está tratando de devolver todas las columnas de una tabla (por ejemplo, una de las tablas en la página vinculada , entonces use esta solución simple, muy poderosa con un 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;

Llamada:

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

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

¿Como funciona esto?

• anyelement es un tipo de anyelement , un tipo polimórfico, un marcador de posición para cualquier tipo de datos que no sean de matriz. Todas las apariciones de un anyelement en la función se evalúan con el mismo tipo proporcionado en tiempo de ejecución. Al suministrar 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 compuesto) para cada tabla creada, por lo que hay un tipo bien definido para cada tabla. Esto incluye tablas temporales, que es conveniente para el uso ad-hoc.

• Cualquier tipo puede ser NULL . Entonces entregamos un valor NULL , lanzamos al tipo de tabla.

• 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 convierten automáticamente en text , los identificadores se duplican automáticamente y se califican según el esquema, si es necesario. Por lo tanto, la inyección SQL no es posible. Esto puede incluso tratar con nombres de tabla calificados por esquema donde quote_ident() fallaría .

Answer 2

Lamento decirlo, pero tu pregunta no está clara. Sin embargo, a continuación encontrará un ejemplo independiente de cómo crear y usar una función que devuelve una variable de cursor. Espero eso ayude !

begin;

create table test (id serial, data1 text, data2 text);

insert into test(data1, data2) values(''one'', ''un'');
insert into test(data1, data2) values(''two'', ''deux'');
insert into test(data1, data2) values(''three'', ''trois'');

create function generate_query(query_name refcursor, columns text[])
returns refcursor 
as $$
begin
  open query_name for execute 
    ''select id, '' || array_to_string(columns, '','') || '' from test order by id'';
  return query_name;
end;
$$ language plpgsql;

select generate_query(''english'', array[''data1'']);
fetch all in english;

select generate_query(''french'', array[''data2'']);
fetch all in french;
move absolute 0 from french; -- do it again !
fetch all in french;

select generate_query(''all_langs'', array[''data1'',''data2'']);
fetch all in all_langs;

-- this will raise in runtime as there is no data3 column in the test table
select generate_query(''broken'', array[''data3'']);

rollback;

Answer 3

Probablemente quieras devolver un cursor . Pruebe 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;