Buenas! estoy trabajando con una función que crea una tabla temporal…seria algo parecido a:
CREATE OR REPLACE FUNCTION pampa.antiguedad_otras(integer)
BEGIN
–CREA TABLA TEMPORAL
CREATE TEMPORARY TABLE tmp_prueba(
x integer
)ON COMMIT DROP;
--INSERTA INFORMACION
INSERT INTO tmp_prueba
SELECT x
FROM tabla;
--RETORNA LOS RESULTADOS
FOR resultado IN SELECT x FROM tmp_prueba LOOP
RETURN NEXT resultado;
END LOOP;
RETURN;
END;
LANGUAGE ‘plpgsql’ VOLATILE;
cuando ejecuto la función: select pampa.antiguedad_otras(50045) , la primera vez se ejecuta de manera correcta, pero cuando la ejecuto por segunda vez, me da el siguiente error: “ERROR: no existe la relación con OID 93031”
y para que funcione de nuevo debo cerrar la sesión y abrir una nueva.
Yo necesito ejecutar esta función para varios legajos pero en la misma sesión, como puedo hacer?
Desde ya muchas gracias!
yo lo primero que pensaria seria en extraer la creacion de la tabla temporaria fuera de la funcion, si mal no calculo eso no intentaria crearte nuevamente la tabla temporaria cuando llamas a la funcion por segunda vez dentro de la misma transaccion?. Puede que el inconveniente pase por ahi, otra opcion es dropear la tabla antes de devolver el resultado de la funcion.
No se que tanto impacto en performance tendra crear y dropear la tabla continuamente en cada llamada de la funcion (eso te lo dejo), pero bueno es una alternativa.
Por otro lado, tienes idea de mas o menos en que momento se produce el error?.. creacion de la tabla, al agregar mas datos, al ciclar?
Hola Richard! La tabla se dropea automáticamente cuando termina la ejecución del programa (porque le puse el ON COMMIT DROP en el momento de la creación). Intenté dropear tabla antes de devolver el resultado de la función, pero me sigue tirando el mismo error y debo cerrar la sesión para volver a ejecutar la función nuevamente sin problemas. El error se produce en el momento en que se intenta crear la tabla cuando se ejecuta la función nuevamente dentro de la misma sesión.
Bueno, no sé si me explico con respecto a lo que quiero decir…
Te hago una consulta, cuantas transacciones/commits tenes por sesion/ejecucion del programa?. Tene en cuenta que si llamas por segunda vez a la funcion sin haber disparado en el medio un commit de manera explicita la tabla temporal aun va a existir. Creo que se soluciona al cerrar la sesion porque simplemente… o se hace un autocommit (depende de la configuracion) o se hace un rollback… en cualquiera de los casos la tabla temporal se dropea.
Para probar tu situacion modifique un poco el algoritmo que pegaste y lo llame simplemente de la siguiente forma:
SELECT antiguedad_otras(id_tabla1)
FROM
tabla_fenomenal
WHERE
campo2 IS NOT NULL;
Si tu utilizacion de la funcion es similar a como la probe yo (dentro de un select con multiples filas)… la unica opcion creo es sacar fuera del SP la creacion de la tabla, colocas la creacion antes de la consulta y listo. Contame un poco mas acerca de como estas usando el SP y si puede o no haber commits parciales en la ejecucion.
Buen día Richard! La llamada a la función la hago sin hacer un commit previamente, es decir select pampa.antiguedad_otras(58208);
CREATE OR REPLACE FUNCTION pampa.antiguedad_otras(integer)
RETURNS integer AS
$BODY$
DECLARE
total integer;
p integer;
elem RECORD ;
par date [2];
consulta text;
BEGIN
CREATE LOCAL TEMPORARY TABLE arreglo(
pos integer,
fechas date [2]
) ON COMMIT DROP;
p:=1;
FOR elem IN SELECT * FROM pampa.dh04 a where a.nro_legaj=$1 and a.tipo_activ ='DC'
and a.aporta_antig_remun=true and a.fec_egres is not null
LOOP
par[1]:= elem.fec_ingre;
par[2]:= elem.fec_egres;
insert into arreglo (pos,fechas) values (p,par);
p:=p+1;
END LOOP ;
--llamada a la funcion calcula_dias();--esta funcion trabaja sobre la tabla temporal 'arreglo'
consulta:='select pampa.calcula_dias()';
EXECUTE consulta into total;
RETURN total;
END;
$BODY$
LANGUAGE ‘plpgsql’ VOLATILE;
Si después del select pampa.antiguedad_otras(58208) quiero ver los registros de la tabla temporal (select * from arreglo) me tira el siguiente error: “relation “arreglo” does not exist” así que aparentemente esta borrando la tabla cuando termina la ejecución del programa. Si le saco el ON COMMIT DROP, ejecuto la función y luego consulto por la tabla temporal entonces si me deja ver sus registros.
De todos modos probé sacando la creación de la tabla fuera de la función y parece quedó bien para lo que yo necesitaba, puedo volver a ejecutar la función sin tener que cerrar la sesión
Muchas gracias por todo!!
CREATE LOCAL TEMP TABLE arreglo AS
(SELECT next_val(‘ap’) as pos, array_append(array_append(‘{}’,fec_ingre),fec_egres)::date as fechas
FROM pampa.dh04 a
WHERE a.nro_legaj=$1 and a.tipo_activ =‘DC’
and a.aporta_antig_remun=true
and a.fec_egres is not null) ON COMMIT DROP;