Cursores, Tablas Temporales Y Pesadillas

[chcma]

Hola de nuevo foreros !!!

Bueno, tengo otra pregunta para mi aprendizaje con PostgreSQL, jejeje. La cuestión es la siguiente, mi intención es crear una tabla temporal con los datos de una tabla original, recorrer un cursor con los datos de la tabla original y modificar un campo de la tabla temporal con un campo de la tabla original.

Para ello he realizado el siguiente código el cual (por eso posteo aqui) no me compila:

Código:

CREATE OR REPLACE FUNCTION pprueba() RETURNS SETOF tusuarios AS
$BODY$	
	--Creo el cursor.
	DECLARE micursor refcursor;
	BEGIN
		--Creo la tabla temporal con los datos de la tabla orignial.
		CREATE TEMPORARY TABLE tusuarios_temp AS SELECT * FROM tusuarios;
		
		--¿Recorro el cursor de la tabla original, cogiendo los campos que necesito
		OPEN micursor FOR SELECT apellido_1 FROM tusuarios
		LOOP
			--¿Asigno a una variable (papellido_1) el valor del campo recogido por el open?
			FETCH NEXT micursor INTO papellido_1
			--¿Modifico el campo de la tabla temporal con el valor de la variable?
			UPDATE tusuarios_temp SET apellido_2=papellido_1
		END LOOP;

		FOR tusuarios_temp 
		LOOP
			--¿Devuelvo los datos de la tabla temporal para mostrarlos en mi aplicacion?
			RETURN NEXT  tusuarios_temp;
		END LOOP;

		DROP TABLE tusuarios_temp;
	END;
$BODY$
LANGUAGE "plpgsql" VOLATILE;

Me imagino que habrá un par de cosas mal, he estado mirando la documentación y bueno.. he llegado hasta aqui, por lo demás estoy un poco estancado, asi que si me pueden orientar, les agradecería que me hecharan un cable.

Se que quizás lo que quiero hacer hay una forma más óptima de realizarlo, pero en principio quiero hacerlo asi para aprender a usar tablas temporales y cursores.

No obstante, también agradecería, de forma secundaria, me dijeran la forma más óptima de resolver dicha cuestión.

Una vez más agradecer a Seyko por la solución sobre la devolución de un cursor en un SP, gracias a tu último post, amigo, he conseguido por fin recibir datos en .NET atraves de un SP de posgre. Lo postearé en breves en .NET ya que he mirado en google y he visto mucha gente perdida con este tema. Nuevamente, gracias por la colaboración de este foro.

[seyko]

Buenas de nuevo charlie,

Varias cosas:

1 La linea de OPEN micursor FOR.... termina en ; (punto y coma)

2 Si abres el cursor con OPEN y lo recorres con FETCH tienes que evaluar la variable FOUND para ver si hay registro que coger. Yo, personalmente, prefiero recorrer los cursores con un FOR

3 CREATE TEMPORARY TABLE.
- Las tablas temporales en Postgres se borran automaticamente al
terminar la sesion (o al hacer COMMIT, si especificar ON COMMIT DROP al
momento de crearla). Por lo tanto los DROP TABLE que tienes por ahi son
innecesarios
- Hay un problema con PL/pgSQL y tablas temporales, el cual hace que
tengas que usar EXECUTE para todas las ordenes que involucren la tabla
temporal (SELECT, INSERT, etc). Es incomodo pero funciona. Es un bug
corregido en 8.3.


Un saludo

[chcma]

Bueno espero poder mirar todo eso hoy, y haber si consigo hacerlo con el FOR, que estos días he estado liado en el curro.

Pero de paso aprovecho para dejar otra pregunta de principiante. En un post que hice hace unas semanas, se me respondia a como devolver los datos de una tabla en un SP, sin necesidad de usar un parametro como refcursor, el SP en cuestion era similar a:

Código:

CREATE OR REPLACE FUNCTION pdameusuarios() RETURNS SETOF tusuarios AS
$BODY$
DECLARE
.
.
.
.

Bueno, la cuestion que me sale ahora es, aqui devolvemos un valor del tipo tusuarios, que como es una tabla ya creada en Postgre, pues entonces entiende el tipo de valor a devolver.

Sin embargo... si quisiera devolver una tabla temporal, tusuarios_temp, la cual tiene mas campos (logicamente ya no puedo devolver un tipo tusuarios), ¿Podria devolver dicho SP sin necesidad de que sea con un refcursor ?

Mi intención es poder trabajar con .NET, por lo que el SP que mostre en el codigo, me viene muy bien. Mi intencion seria usar esa misma definicion pero que me sirva tambien para devolver tablas temporales.

Algo como: (Fijarse en TABLE)
CREATE OR REPLACE FUNCTION pdameusuarios() RETURNS SETOF TABLE AS

Vamos, algo que me permita devolver tablas, sin tener que poner que tipo de tabla es especificamente.

¿Eso sería factible?

[seyko]

Buenas, recuerdo ese post, creo te repondi.

Ahora a lo que vamos, si quieres devolver un registro de cualquier tabla luego tienes que decirle el tipo de datos que devuelve al hacer select sobre ella.

Ejemplo:
create or replace function pdameusuarios() RETURNS SETOF record AS
$BODY$
DECLARE
reg record;
BEGIN
FOR reg IN (select "campos" from tabla)
LOOP
return next reg;
END LOOP;
END;
$BODY$ language 'plpgsql';

supongamos que "campos" son id::integer y nombre::varchar
la select para llamar a esta funcion seria algo asi:

select id, nombre from pdameusuarios() as foo(id integer, nombre varchar);


Un saludo

[chcma]

Gracias, voy a probar todo esto y mas este fin de semana como un condenado. Por cierto, seguro tienes el certificado de postgre, no?, jajajaja

Muchas gracias.

[yoMax]

Hola, yo tengo un problema un tanto raro
necesito hacer una funcion que cree una tabla temporal, pero me sale un error:
ERROR: cannot open SELECT INTO query as cursor
CONTEXT: PL/pgSQL function "compmarca" line 23 at open
La consulta esta bien, porque cuando la hago a parte, si me crea la tabla y la llena, algo tiene que ver con la llamada al cursor. si pueden ayudarme les agradecería.

CREATE OR REPLACE FUNCTION "reportes"."compmarca" (varchar, varchar, varchar, varchar) RETURNS SETOF boolean AS
$body$
declare
categoria alias for $1;
campana alias for $2;
fechaini alias for $3;
fechafin alias for $4;
cursor1 refcursor;
consulta varchar;

begin
consulta:='CREATE TEMP TABLE compxmarca as
select comp.* from (
select * from reportes.compxmarca('''||categoria||''','''||campa na||''',''tv'','''||fechaini||''','''||fechafin||' '')
union
select * from reportes.compxmarca('''||categoria||''','''||campa na||''',''rd'','''||fechaini||''','''||fechafin||' '')
union
select * from reportes.compxmarca('''||categoria||''','''||campa na||''',''pr'','''||fechaini||''','''||fechafin||' '')
union
select * from reportes.compxmarca('''||categoria||''','''||campa na||''',''rv'','''||fechaini||''','''||fechafin||' '')
union
select * from reportes.compxmarca('''||categoria||''','''||campa na||''',''su'','''||fechaini||''','''||fechafin||' '')
) as comp;';

open cursor1 for execute consulta;
close cursor1;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

[yoMax]

Tengo otro problema, cambie la funcion y esta si me resulta pero cuando quiero hacerle un select a la tabla o incluso un drop me sale que no existe la tabla, pero si esta creada, es como si la funcion creara una sesion para la creacion de la tabla y la tabla perteneciera a esa sesiosn y por eso en el editor no puedo verla ser?

esta es la nueva funcion


CREATE OR REPLACE FUNCTION "reportes"."compmarca" (varchar, varchar, varchar, varchar) RETURNS SETOF boolean AS
$body$
declare
categoria alias for $1;
campana alias for $2;
fechaini alias for $3;
fechafin alias for $4;
cursor1 refcursor;
consulta varchar;
respuesta varchar;
begin


EXECUTE 'CREATE GLOBAL TEMP TABLE compxmarcatb (
medio varchar(2),
categoria varchar(6),
marca varchar(6),
inversion numeric
)';
INSERT INTO compxmarcatb (select comp.* from (
select * from reportes.compxmarca(''||categoria||'',''||campana| |'','tv',''||fechaini||'',''||fechafin||'')
union
select * from reportes.compxmarca(''||categoria||'',''||campana| |'','rd',''||fechaini||'',''||fechafin||'')
union
select * from reportes.compxmarca(''||categoria||'',''||campana| |'','pr',''||fechaini||'',''||fechafin||'')
union
select * from reportes.compxmarca(''||categoria||'',''||campana| |'','rv',''||fechaini||'',''||fechafin||'')
union
select * from reportes.compxmarca(''||categoria||'',''||campana| |'','su',''||fechaini||'',''||fechafin||'')
) as comp);


end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

[seyko]

yoMax no postees repetido por favor!!!!
Y no robes post, habre un post nuevo con el tema.

La primera ya te conteste en tu post... y veo que lo has solucionado, por lo que podrias haberlo dicho y poner la solucion (para el que lea el post despues) y no nos haces contestar a algo innecesario.
La segunda pregunta no te dice nada lo de tabla "TEMPORAL" si buscas en la documentación de postgres (es muy buena) o si simplemente te lees ESTE HILO tendras la respuesta a tu pregunta.

Salu2