tablas temporales en funciones

General PostgreSQL
1

Aca les dejo la función, la misma esta compilando y funcionando. pero solo la primera vez! despues esta dando error de inserción de datos en la tabla temporal, por lo que estuve viendo, le asigna un OID a la tabla temporal y esto genera el conflicto.

CREATE OR REPLACE FUNCTION get_plan_version_contenido_info(integer)
RETURNS SETOF record AS
$BODY$
DECLARE

id_plan_version ALIAS for $1;
modulo_raiz record;
resultado record;
c_pv_activo int;
c_pv_no_activo int;

BEGIN

CREATE LOCAL TEMP TABLE tmp1
( elemento int,
elemento_tipo character(1),
elemento_subtipo smallint,
elemento_clase_gui character varying(100),
elemento_padre integer,
nombre character varying(255),
modulo_revision int,
modulo_revision_comp integer,
orden smallint,
cant_pv_activo smallint,
cant_pv_no_activo smallint);

INSERT INTO tmp1 ( elemento, elemento_tipo, elemento_subtipo, elemento_clase_gui, elemento_padre, nombre, modulo_revision, modulo_revision_comp, orden )
SELECT * FROM get_plan_version_contenido ( id_plan_version )
AS ( elemento int,
elemento_tipo character(1),
elemento_subtipo smallint,
elemento_clase_gui character varying(100),
elemento_padre integer,
nombre character varying(255),
modulo_revision int,
modulo_revision_comp integer,
orden smallint );

FOR resultado IN ( SELECT * FROM tmp1 ) LOOP

 SELECT COUNT(*)  INTO c_pv_no_activo
   FROM sga_modulo_revision_plan mrp, sga_planes_versiones pv 
  WHERE mrp.modulo_revision = resultado.modulo_revision
    AND mrp.plan_version = pv.plan_version
    AND pv.estado IN ( 'N', 'B') ;

SELECT COUNT(*)  INTO c_pv_activo
   FROM sga_modulo_revision_plan mrp, sga_planes_versiones pv 
  WHERE mrp.modulo_revision = resultado.modulo_revision
    AND mrp.plan_version = pv.plan_version
    AND pv.estado IN ( 'A', 'V') ;

resultado.cant_pv_activo := c_pv_activo;
resultado.cant_pv_no_activo := c_pv_no_activo;

RETURN NEXT resultado ;

END LOOP;

DROP TABLE tmp1;

END$BODY$
LANGUAGE ‘plpgsql’ VOLATILE;
ALTER FUNCTION get_planes_versiones_contenido() OWNER TO dba;

A la función la llamo asi:

SELECT *
FROM get_plan_version_contenido_info(1)
AS( elemento integer,
elemento_tipo character(1),
elemento_subtipo smallint,
elemento_clase_gui character varying(100),
elemento_padre integer,
nombre character varying(255),
modulo_revision integer,
modulo_revision_comp integer,
orden smallint ,
cant_pv_activo smallint,
cant_pv_no_activo smallint);

el codigo de error que me esta dando el pgadmin en la segunda corrida:

ERROR: relation with OID 22163 does not exist
SQL state: 42P01
Context: SQL statement “INSERT INTO tmp1 ( elemento, elemento_tipo, elemento_subtipo, elemento_clase_gui, elemento_padre, nombre, modulo_revision, modulo_revision_comp, orden ) SELECT * FROM get_plan_version_contenido ( $1 ) AS ( elemento int, elemento_tipo character(1), elemento_subtipo smallint, elemento_clase_gui character varying(100), elemento_padre integer, nombre character varying(255), modulo_revision int, modulo_revision_comp integer, orden smallint )”
PL/pgSQL function “get_plan_version_contenido_info” line 24 at SQL statement

2

Hola Darío, bienvenido al foro!

Que versión de postgres estas usando? es inferior a la 8.3? Creo que es un bug conocido y arreglado recién para esta versión.
Si tenes una 8.3 a mano, probala y fijate si anda.

Según lo que leí en los foros de postgres, esto es por que las querys se precompilan y los planes para las mismas se tratan de cachear. Entonces cuando borras la tabla se invalida el plan que ya estaba hecho. Ahí sugieren que en vez de hacer un DROP pruebes con TRUNCATE.

Fuente: http://archives.postgresql.org//pgsql-novice/2007-08/msg00097.php

Saludos, Esteban.

3

Efectivamente es como dice Esteban! En 8.3 funciona perfecto.
Lo que podes probar es usar tirar todas las sentencias sql con el comando EXECUTE (Ej: execute ‘select * from tmp;’:wink:

Lo de truncate no creo que te sirva si lo que necesitas es borrar la tabla. Truncate te borra solo los datos.

Una opcion para eliminar la tabla temporal es usar “on commit drop” en la creación de la misma.

Ejemplo:

CREATE LOCAL TEMP TABLE tmp1
( elemento int,
elemento_tipo character(1),
elemento_subtipo smallint,
elemento_clase_gui character varying(100),
elemento_padre integer,
nombre character varying(255),
modulo_revision int,
modulo_revision_comp integer,
orden smallint,
cant_pv_activo smallint,
cant_pv_no_activo smallint) on commit drop;

Creo que con una combinación de execute y on commit drop lo podrias hacer funcionar en 8.2.

Espero que te sea de ayuda. Un abrazo.
Nico.