Re: [pgsql-es-ayuda] Error en función para devolver todos los records en un refcursor

El 18 de agosto de 2011 11:10, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>escribió:

> Excerpts from Marcos Luis Ortiz Valmaseda's message of jue ago 18 11:30:57
> -0400 2011:
> > Saludos a los listeros
> > Estoy teniendo problemas con una función que debe devolver todos los
> records
> > de una determinada función, que devuelve un refcursor.
> > Acá les dejo el código:
> >
> > CREATE OR REPLACE FUNCTION return_refcursor_fetch_all(text, refcursor,
> > anyarray)
> > RETURNS REFCURSOR AS $$
> > DECLARE
> > v_func ALIAS FOR $1; -- Funcion original
> > v_ref refcursor; -- Ref cursor de la funcion original
> > v_ref_2 refcursor;
> > v_params ALIAS FOR $3; -- Parametros de la funcion
> > v_array_position int4; -- Posicion del arreglo para la comparacion
> > v_temp_text text ; -- Variable temporal para la consulta
> > v_cant_params int4;
> > BEGIN
> > -- Insertar codigo de la funcion en forma de texto y ejecutarla a
> una
> > variable
> > v_array_position := 0;
> > v_temp_text := '';
> > v_cant_params := array_lenght(v_params, 1); -- Calcula la cantidad
> de
> > parametros
> >
> > -- Recorrer todo el array para construir la candena de los
> parametros
> > WHILE v_array_position <= cant_params LOOP
> > v_temp_text := (v_temp_text ||', ' ||
> > (v_params[v_array_position])::text); -- Concatena todo y construye la
> > cadena de los parametros
> > END LOOP;
> > -- Ejecutar la consulta construida dinamicamente usando EXECUTE
> > EXECUTE 'SELECT * FROM '|| v_func ||'('|| v_temp_text || ') INTO '
> ||
> > v_ref || ';';
> > OPEN v_ref_2 FOR SELECT * FROM v_ref;
> > RETURN v_ref_2;
> > END;
> > $$ LANGUAGE plpgsql;
> >
> > Y la forma que la estamos usando es así:
> > select * from
> > return_refcursor_fetch_all('schema_auditoria.fn_consultar_transaccion',
> > 'p_cursor_registros',
> >
> '{null,null,null,null,null,null,null,null,p_cursor_numregistros,p_cursor_registros}');
>
> No entiendo la intención de tu función. Si asumes que los parámetros
> son todos text o convertibles a text a través de un cast, ¿por qué no
> pasas text[] en vez de anyarray? Creo que el problema es la invocación
> a v_func, es decir la función que pasas como parámetro, no
> return_refcursor_fetch_all, pero no estoy seguro.
>
> ¿Qué estás tratando de hacer? La verdad es que no veo el propósito de
> todo esto. Tomas una función que retorna un refcursor, luego abres ese
> refcursor, metes los resultados en otro refcursor y retornas el segundo
> refcursor ... ¿?
>
> --
> Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
>

No, ya estoy cambiando eso, lo que debe devolver es un SET OF RECORD, y ya
la estoy cambiando para eso.
El sentido principal es que estamos usando una aplicación para la generación
de reporte, y la misma usa procedimientos almacenados en la base de datos,
pero
dicha base de datos, tiene todos los procedimientos programados que
devuelven un refcursor. La idea general es hacer una función que sirva como
de proxy a dicho procedimientos y devolver todos los datos, no con
refcursor, sino como un set de datos, para el cual se le puedan aplicar
ORDER BY, GROUP BY, LIMIT, etc.

Éste es un ejemplo de las funciones que tienen acá:

CREATE OR REPLACE FUNCTION schema_organizacion.fn_consultar_cargo(p_id
integer, p_nombre character varying, p_ordenamiento character varying,
p_numpagina integer, p_elementosporpagina integer, p_totalregistros integer,
p_cursor_numregistros refcursor, p_cursor_registros refcursor)
RETURNS SETOF refcursor AS
$BODY$
DECLARE
v_sqlid varchar;
v_sqlnombre varchar;
v_sqlordenamiento varchar;
v_sqlpaginacion varchar;
v_maxumpaginas float4;
v_sqlcondicion varchar;
v_execsql varchar;
v_execsqlcount varchar;
v_joins varchar;
BEGIN
v_sqlid := '';
v_sqlnombre := '';
v_sqlordenamiento := '';
v_sqlpaginacion := '';
v_maxumpaginas := 0;
v_sqlcondicion := '';
v_execsql := '';
v_execsqlcount := '';
v_joins := '';

IF (nullvalue(p_id) != TRUE) THEN
v_sqlid := ' AND c.id = ' || p_id;
END IF;
IF (nullvalue(p_nombre) != TRUE) THEN
v_sqlnombre := ' AND lower(c.nombre) like ' || quote_literal('%' ||
lower(p_nombre) || '%');
END IF;
IF (nullvalue(p_ordenamiento) != TRUE) THEN
v_sqlordenamiento := ' ORDER BY ' || p_ordenamiento;
END IF;

v_joins := '';
v_sqlcondicion := v_joins || ' WHERE true ' || v_sqlid || v_sqlnombre;

IF ((p_numpagina != 0) AND (p_elementosporpagina != 0)) THEN
v_sqlpaginacion := ' LIMIT ' || p_elementosporpagina || ' OFFSET ' ||
((p_numpagina-1)*p_elementosporpagina);
END IF;

IF (p_totalregistros = 0) THEN
v_execsqlcount := 'SELECT COUNT(*) FROM tbl_cargo c' || v_sqlcondicion;
OPEN p_cursor_numregistros FOR EXECUTE v_execsqlcount;
RETURN NEXT p_cursor_numregistros;
ELSE
v_maxumpaginas := ceil(p_totalregistros/p_elementosporpagina);
IF (v_maxumpaginas<p_numpagina) THEN
IF ((p_numpagina != 0) AND (p_elementosporpagina != 0)) THEN
v_sqlpaginacion := ' LIMIT ' || p_elementosporpagina || ' OFFSET '
|| ((v_maxumpaginas)*p_elementosporpagina);
END IF;
END IF;
END IF;
v_execsql := 'SELECT c.id as id,c.nombre as nombre FROM tbl_cargo c ' ||
v_sqlcondicion || v_sqlordenamiento || v_sqlpaginacion;
--RAISE NOTICE '%', v_execsql;
--BEGIN; SELECT * FROM fn_consultar_cargo
(NULL,NULL,NULL,0,0,0,'cnr','cr'); FETCH ALL IN cr; END;
OPEN p_cursor_registros FOR EXECUTE v_execsql;
RETURN NEXT p_cursor_registros;
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

Entonces, la función return_fetch_all o como se llame, lo que queremos es
que tome todos los datos del refcursor y devuelva un SET OF RECORDS para
poder aplicarle lo que ya te he dicho (ORDER BY, GROUP BY, LIMIT), porque
al tipo refcursor no se le puede hacer.

--
Marcos Luis Ortíz Valmaseda
Software Engineer (UCI)
Linux User # 418229
http://marcosluis2186.posterous.com
http://www.linkedin.com/in/marcosluis2186
https://fedoraproject.org/wiki/User:Marcosluis