Vous n'êtes pas identifié(e).

Pages : 1 2

#1 17/08/2011 18:44:57

charleydc5
Membre

Table temporaire dans une fonction

Bonjour à tous,

Dans certaines requêtes MSSQL, nous avons utilisé des tables temporaires pour faire des traitements spécifiques. Par exemple : 

DECLARE @Cas TABLE
	(intCasId INT,
	strNoDossier VARCHAR(50),
	strConseillerNom VARCHAR(255),
	intLocalisationId INT,
	strLocalisationNom VARCHAR(255),
	intRegionId INT,
	strRegionNom VARCHAR(255),
	strActiviteNom VARCHAR(100),
	intTemps INT,
	intCouts INT,
	strDossierEtatNom VARCHAR(255)
	)
	
	INSERT INTO @Cas(intCasId, strNoDossier, strConseillerNom, intLocalisationId, strLocalisationNom, intRegionId, strRegionNom, strActiviteNom, intTemps, intCouts, strDossierEtatNom)
[...] une requête SELECT qui alimente l'instruction INSERT ci-dessus
Plusieurs autres traitements suivent, et à la fin je fais SELECT * FROM @Cas, et c'est le "resultset" qui est envoyé à mon rapport (JasperSoft iReport).

J'aimerais reproduire la même chose en PostgreSQL. J'ai lu sur les tables temporaires, mais j'aimerais savoir si c'est possible de déclarer une table "virtuelle" pour l'exécution de la fonction seulement?

Merci beaucoup

Charles Morin
PostgreSQL 9.0.3 sur Windows 2008 Server Standard (x64)

Hors ligne

#2 17/08/2011 18:48:52

gleu
Administrateur

Re : Table temporaire dans une fonction

Les tables temporaires sont automatiquement détruites à la fin de la session. Cependant, elles peuvent aussi l'être au COMMIT avec l'option "ON COMMIT DROP". Donc ça change un peu de MSSQL car vous ne pouvez pas faire le DECLARE. Vous devez passer par un CREATE TEMPORARY TABLE... ON COMMIT DROP. Quant au INSERT, ça se passe comme pour tout autre INSERT : INSERT INTO Cas(....) VALUES (...).

Guillaume.

Hors ligne

#3 17/08/2011 18:51:18

charleydc5
Membre

Re : Table temporaire dans une fonction

D'accord. J'ai fait plusieurs tests ce matin avec cette commande mais lorsque j'arrivais au INSERT, PostgreSQL me disait que le nom de ma table temporaire n'existait pas (relation does not exist).

Faut-il mettre un prefix comme $ pour les paramètres?

Merci !

Charles Morin
PostgreSQL 9.0.3 sur Windows 2008 Server Standard (x64)

Hors ligne

#4 17/08/2011 19:21:43

charleydc5
Membre

Re : Table temporaire dans une fonction

Voici le code de la fonction:

PREPARE t_cas AS
		SELECT Cas.intCasId,
		Cas.strNoDossier,
		Users.strUserFirstName || ' ' || Users.strUserLastName AS strConseillerNom,
		Localisation.intLocalisationId,

		CASE $5
			WHEN 'fr' THEN Localisation.strLocalisationNomFr
			WHEN 'en' THEN Localisation.strLocalisationNomEn
		END AS strLocalisationNom,

		Region.intRegionId,
		CASE $5
			WHEN 'fr' THEN Region.strRegionNomFr
			WHEN 'en' THEN Region.strRegionNomEn
		END AS strRegionNom,

		CASE $5
			WHEN 'fr' THEN ActiviteNom.strActiviteNomFr
			WHEN 'en' THEN ActiviteNom.strActiviteNomEn
		END AS strActiviteNom,

		Cheminement.intTemps,
		Cheminement.intCouts,
		CASE $5
			WHEN 'fr' THEN DossierEtat.strDossierEtatAbrFr
			WHEN 'en' THEN DossierEtat.strDossierEtatAbrEn 
		END AS strDossierEtatNom

		FROM stirq.Cas

		INNER JOIN stirq.DossierEtat ON
		stirq.Cas.intDossierEtatId = stirq.DossierEtat.intDossierEtatId

		INNER JOIN stirq.Localisation ON
		stirq.Cas.intLocalisationId = stirq.Localisation.intLocalisationId

		INNER JOIN stirq.Region ON
		stirq.Localisation.intRegionId = stirq.Region.intRegionId

		INNER JOIN stirq.Intervention ON
		stirq.Cas.intCasId = stirq.Intervention.intCasId

		INNER JOIN stirq.ActiviteNom ON
		stirq.Intervention.intActiviteNomId = stirq.ActiviteNom.intActiviteNomId

		INNER JOIN stirq.Cheminement ON
		stirq.Intervention.intInterventionId = stirq.Cheminement.intInterventionId
			
		WHERE 
		Cheminement.datCheminement BETWEEN $3 AND $4 AND
		Region.intRegionId = $1

		ORDER BY Cas.strNoDossier, ActiviteNom.intOrder;

	CREATE TEMP TABLE t_cas WITH (OIDS) ON COMMIT DROP AS
	EXECUTE t_cas;

	INSERT INTO t_cas
	SELECT	0,
		NULL,
		NULL,
		0,
		NULL,
		$1,
		$2,
		CASE $5
			WHEN 'fr' THEN stirq.ActiviteNom.strActiviteNomFr
			WHEN 'en' THEN stirq.ActiviteNom.strActiviteNomEn
		END AS strActiviteNom,
		NULL,
		NULL,
		NULL
	
	FROM	stirq.ActiviteNom
	WHERE	intActiviteTypeId = 4;
                 
	SELECT * FROM t_cas;

J'ai le message suivant :
ERROR:  relation "t_cas" does not exist
LINE 156:  INSERT INTO t_cas(intCasId, strNoDossier, strConseillerNom,...

J'ai suivi les instructions à la page 917 dans le manuel de PostgreSQL 9.0.3 (CREATE TABLE AS)

Merci!

Dernière modification par charleydc5 (18/08/2011 14:15:20)

Charles Morin
PostgreSQL 9.0.3 sur Windows 2008 Server Standard (x64)

Hors ligne

#5 17/08/2011 20:57:20

gleu
Administrateur

Re : Table temporaire dans une fonction

Pas d'erreur à l'exécution du PREPARE et du CREATE TEMP TABLE ? et qu'y a-t-il dans le [...] ?

Guillaume.

Hors ligne

#6 18/08/2011 13:51:16

charleydc5
Membre

Re : Table temporaire dans une fonction

Non, la seule erreur que le compilateur me donne est le t_cas does not exist.

En fait, l'instruction INSERT suit immédiatement le EXECUTE. Les [...] sont inutiles... désolé

Charles Morin
PostgreSQL 9.0.3 sur Windows 2008 Server Standard (x64)

Hors ligne

#7 18/08/2011 13:54:04

charleydc5
Membre

Re : Table temporaire dans une fonction

J'ai ajouté le détail de mon INSERT dans le post #4.

Charles Morin
PostgreSQL 9.0.3 sur Windows 2008 Server Standard (x64)

Hors ligne

#8 18/08/2011 13:59:18

gleu
Administrateur

Re : Table temporaire dans une fonction

Ah, je crois avoir compris. Vous n'êtes pas dans une transaction. Du coup, la fin du CREATE TEMP TABLE supprime la table, ce qui fait que le INSERT est en erreur. Me trompe-je?

Guillaume.

Hors ligne

#9 18/08/2011 14:01:14

charleydc5
Membre

Re : Table temporaire dans une fonction

Ce serait effectivement très logique! Par contre, j'avais essayé d'enlever ON COMMIT DROP et de faire le drop moi-même à la fin de la fonction... même résultat.

L'instruction CREATE était ainsi :
CREATE TEMP TABLE t_cas WITH (OIDS) AS
EXECUTE t_cas;

.. suivi de l'instruction INSERT

Charles Morin
PostgreSQL 9.0.3 sur Windows 2008 Server Standard (x64)

Hors ligne

#10 18/08/2011 14:07:30

gleu
Administrateur

Re : Table temporaire dans une fonction

Étonnant, je n'ai aucun problème avec ça. Voici mon test:

b1=# create or replace function titi() returns text language plpgsql
as $$
declare
  tmp text;
begin
  prepare toto  as select current_query from pg_stat_activity;
  create temp table toto on commit drop as execute toto;
  select into tmp * from toto limit 1;
  deallocate toto;
  return tmp;
end
$$;
CREATE FUNCTION
b1=# select titi();
      titi      
----------------
 select titi();
(1 row)

b1=# select titi();
      titi      
----------------
 select titi();
(1 row)

b1=# select titi();
      titi      
----------------
 select titi();
(1 row)

b1=# select * from toto;
ERREUR:  la relation « toto » n'existe pas
LINE 1: select * from toto;
                      ^
b1=# select titi(), 2;
       titi        | ?column? 
-------------------+----------
 select titi(), 2; |        2
(1 row)

b1=# select titi(), true;
         titi         | bool 
----------------------+------
 select titi(), true; | t
(1 row)

Guillaume.

Hors ligne

#11 18/08/2011 14:10:10

gleu
Administrateur

Re : Table temporaire dans une fonction

Même si je ne pense pas que ce soit le problème, il y a un soucis dans le INSERT : il y a un point-virgule après un NULL.

Guillaume.

Hors ligne

#12 18/08/2011 14:18:38

charleydc5
Membre

Re : Table temporaire dans une fonction

J'ai enlevé le point virgule et c'est la même chose.

Est-ce que ça pourrait avoir un lien avec mon schéma?

J'ai toutefois tenté de spécifier stirq. avant t_cas et j'ai le même problème..

Charles Morin
PostgreSQL 9.0.3 sur Windows 2008 Server Standard (x64)

Hors ligne

#13 18/08/2011 14:34:43

gleu
Administrateur

Re : Table temporaire dans une fonction

Les tables temporaires sont des schémas à part, donc non, ça ne peut pas être le problème.

Guillaume.

Hors ligne

#14 18/08/2011 15:33:47

dverite
Membre

Re : Table temporaire dans une fonction

charleydc5 a écrit :

J'ai enlevé le point virgule et c'est la même chose.

Est-ce que ça pourrait avoir un lien avec mon schéma?

J'ai toutefois tenté de spécifier stirq. avant t_cas et j'ai le même problème..

C'est-à-dire que tu as une table stirq.t_cas qui existe et que INSERT INTO stirq.t_cas sort un message d'erreur disant qu'elle n'existe pas?
A ce niveau là on peut se demander si tu exécutes bien la version de la fonction que tu modifies.
Par exemple il  pourrait y avoir une version dans le schéma public, une autre avec le même nom dans le schéma stirq.
Si à l'exécution il va chercher la fonction dans l'autre schéma, ça expliquerait que quoi que tu essaies ça ne change rien à l'erreur (qui n'a pas lieu d'être).
Il serait intéressant de vérifier que ton CREATE FUNCTION spécifie ou non un schéma, quel est le search_path au moment du CREATE FUNCTION, et est-ce que c'est le même search_path qu'au moment de l'exécution de la fonction, et enfin est-ce qu'il y a plusieurs instances de la fonction créées dans des schémas différents.

@DanielVerite
http://blog-postgresql.verite.pro/

Hors ligne

#15 18/08/2011 16:06:12

charleydc5
Membre

Re : Table temporaire dans une fonction

La fonction n'est même pas encore créée, je ne réussi pas à la faire compiler.

Voici le code d'entête de ma fonction :

CREATE OR REPLACE FUNCTION stirq.qryCasTempsCoutsTypeRpt(
	pvintRegionId INT,
	pvstrRegionNom VARCHAR(100),
	pvdatFrom TIMESTAMP,
	pvdatTo TIMESTAMP,
	pvstrLangue VARCHAR(10)
)

RETURNS TABLE(
	intCasId INT,
	strNoDossier VARCHAR(50),
	strConseillerNom VARCHAR(255),
	intLocalisationId INT,
	strLocalisationNom VARCHAR(255),
	intRegionId INT,
	strRegionNom VARCHAR(255),
	strActiviteNom VARCHAR(100),
	intTemps INT,
	intCouts INT,
	strDossierEtatNom VARCHAR(255)
  ) AS
  
$BODY$

[...]

Je n'ai qu'un seul schéma dans cette base de données, et il est nommé "stirq". J'ai supprimé le schéma public.

Si je roule SHOW search_path, j'ai le résultat ""$user",public"

Merci

Charles Morin
PostgreSQL 9.0.3 sur Windows 2008 Server Standard (x64)

Hors ligne

#16 18/08/2011 16:42:14

dverite
Membre

Re : Table temporaire dans une fonction

charleydc5 a écrit :

La fonction n'est même pas encore créée, je ne réussi pas à la faire compiler.

Je pensais que c'était une erreur d'exécution parce que le message d'erreur initial, celui-ci:

ERROR:  relation "t_cas" does not exist
LINE 156:  INSERT INTO t_cas(intCasId, strNoDossier, strConseillerNom,...

est visiblement une erreur d'exécution, pas une erreur de création de fonction.
Est-ce que tu pourrais copier-coller dans la discussion l'intégralité du create function dans sa version actuelle, message d'erreur inclus?

@DanielVerite
http://blog-postgresql.verite.pro/

Hors ligne

#17 18/08/2011 16:43:10

gleu
Administrateur

Re : Table temporaire dans une fonction

Merci de présenter la fonction entière ainsi que le message d'erreur exact.

Guillaume.

Hors ligne

#18 18/08/2011 16:43:34

charleydc5
Membre

Re : Table temporaire dans une fonction

Je pense que j'ai mis le doigt sur le problème, mais je ne comprend pas pourquoi PostgreSQL agit ainsi.

J'ai testé le code suivant dans pgAdmin :

CREATE TEMP TABLE temptest(col INTEGER);

INSERT INTO temptest VALUES (1);
INSERT INTO temptest VALUES (2);
INSERT INTO temptest VALUES (3);

SELECT * FROM temptest;

En appuyant sur Execute Query, je vois le résultat de la requête, donc :
1
2
3

Si je réappuie sur Execute Query, j'ai le message suivant comme mon problème initial:
ERROR:  relation "temptest" already exists


********** Error **********

ERROR: relation "temptest" already exists
SQL state: 42P07



Vraiment bizzare... quelqu'un a une idée??

Charles Morin
PostgreSQL 9.0.3 sur Windows 2008 Server Standard (x64)

Hors ligne

#19 18/08/2011 16:45:01

charleydc5
Membre

Re : Table temporaire dans une fonction

Voici la fonction au complet :

CREATE OR REPLACE FUNCTION stirq.qryCasTempsCoutsTypeRpt(
	pvintRegionId INT,
	pvstrRegionNom VARCHAR(100),
	pvdatFrom TIMESTAMP,
	pvdatTo TIMESTAMP,
	pvstrLangue VARCHAR(10)
)

RETURNS TABLE(
	intCasId INT,
	strNoDossier VARCHAR(50),
	strConseillerNom VARCHAR(255),
	intLocalisationId INT,
	strLocalisationNom VARCHAR(255),
	intRegionId INT,
	strRegionNom VARCHAR(255),
	strActiviteNom VARCHAR(100),
	intTemps INT,
	intCouts INT,
	strDossierEtatNom VARCHAR(255)
  ) AS
  
$BODY$

	PREPARE t_cas AS
		SELECT Cas.intCasId AS intCasIdGlobal,
		Cas.strNoDossier,
		Users.strUserFirstName || ' ' || Users.strUserLastName AS strConseillerNom,
		Localisation.intLocalisationId,

		CASE $5
			WHEN 'fr' THEN Localisation.strLocalisationNomFr
			WHEN 'en' THEN Localisation.strLocalisationNomEn
		END AS strLocalisationNom,

		Region.intRegionId,
		CASE $5
			WHEN 'fr' THEN Region.strRegionNomFr
			WHEN 'en' THEN Region.strRegionNomEn
		END AS strRegionNom,

		CASE $5
			WHEN 'fr' THEN ActiviteNom.strActiviteNomFr
			WHEN 'en' THEN ActiviteNom.strActiviteNomEn
		END AS strActiviteNom,

		Cheminement.intTemps,
		Cheminement.intCouts,
		CASE $5
			WHEN 'fr' THEN DossierEtat.strDossierEtatAbrFr
			WHEN 'en' THEN DossierEtat.strDossierEtatAbrEn 
		END AS strDossierEtatNom

		FROM stirq.Cas

		INNER JOIN stirq.DossierEtat ON
		stirq.Cas.intDossierEtatId = stirq.DossierEtat.intDossierEtatId

		INNER JOIN stirq.Localisation ON
		stirq.Cas.intLocalisationId = stirq.Localisation.intLocalisationId

		INNER JOIN stirq.Region ON
		stirq.Localisation.intRegionId = stirq.Region.intRegionId

		INNER JOIN stirq.Intervention ON
		stirq.Cas.intCasId = stirq.Intervention.intCasId

		INNER JOIN stirq.ActiviteNom ON
		stirq.Intervention.intActiviteNomId = stirq.ActiviteNom.intActiviteNomId

		INNER JOIN stirq.Cheminement ON
		stirq.Intervention.intInterventionId = stirq.Cheminement.intInterventionId
		
		INNER JOIN 

			(SELECT	stirq.Cas.intCasId,
			MAX(intCheminementId) AS intLastCheminementId

			FROM stirq.Cheminement 

			INNER JOIN stirq.Intervention ON
			stirq.Cheminement.intInterventionId = stirq.Intervention.intInterventionId

			INNER JOIN stirq.Cas ON
			stirq.Intervention.intCasId = stirq.Cas.intCasId
					
			INNER JOIN stirq.Localisation ON
			stirq.Cas.intLocalisationId = stirq.Localisation.intLocalisationId

			INNER JOIN stirq.Region ON
			stirq.Localisation.intRegionId = stirq.Region.intRegionId
					
			WHERE 
				stirq.Cheminement.datCheminement BETWEEN $3 AND $4 AND
				Region.intRegionId = $1

			GROUP BY Cas.intCasId
			) AS vwsLastCheminementCas ON 
		
			Cas.intCasId = vwsLastCheminementCas.intCasId
			
		INNER JOIN (
			SELECT	Cheminement.intCheminementId,
					Cheminement.intConseiller1Id
					
			FROM	stirq.Cheminement
			
			INNER JOIN stirq.Intervention ON
			stirq.Cheminement.intInterventionId = stirq.Intervention.intInterventionId

			INNER JOIN stirq.Cas ON
			stirq.Intervention.intCasId = stirq.Cas.intCasId
					
			INNER JOIN stirq.Localisation ON
			stirq.Cas.intLocalisationId = stirq.Localisation.intLocalisationId

			INNER JOIN stirq.Region ON
			stirq.Localisation.intRegionId = stirq.Region.intRegionId
			
			WHERE 
				stirq.Cheminement.datCheminement BETWEEN $3 AND $4 AND
				stirq.Region.intRegionId = $1
		
		) AS vwsLastConseillerCas ON
		vwsLastCheminementCas.intLastCheminementId = vwsLastConseillerCas.intCheminementId
		
		INNER JOIN stirq.Users ON
		vwsLastConseillerCas.intConseiller1Id = stirq.Users.intUserId

		WHERE 
			Cheminement.datCheminement BETWEEN $3 AND $4 AND
			Region.intRegionId = $1

		ORDER BY Cas.strNoDossier, ActiviteNom.intOrder;

	CREATE TEMP TABLE t_cas AS EXECUTE t_cas;
	
	INSERT INTO t_cas
	SELECT	0,
		NULL,
		NULL,
		0,
		NULL,
		$1,
		$2,
		CASE $5
			WHEN 'fr' THEN stirq.ActiviteNom.strActiviteNomFr
			WHEN 'en' THEN stirq.ActiviteNom.strActiviteNomEn
		END AS strActiviteNom,
		NULL,
		NULL,
		NULL
	
	FROM	stirq.ActiviteNom
	WHERE	intActiviteTypeId = 4;
   
	SELECT * FROM t_cas;
	
$BODY$

  LANGUAGE sql VOLATILE
  COST 100;

Message d'erreur :
ERROR:  relation "t_cas" does not exist
LINE 140:  INSERT INTO t_cas
                       ^


********** Error **********

ERROR: relation "t_cas" does not exist
SQL state: 42P01
Character: 3683

Dernière modification par charleydc5 (18/08/2011 16:45:24)

Charles Morin
PostgreSQL 9.0.3 sur Windows 2008 Server Standard (x64)

Hors ligne

#20 18/08/2011 16:45:17

gleu
Administrateur

Re : Table temporaire dans une fonction

Pas bizarre du tout. La table temporaire existe tout le temps de la session (donc tant qu'il n'y a pas déconnexion de l'éditeur de requête dans votre cas).

Guillaume.

Hors ligne

#21 18/08/2011 16:47:12

charleydc5
Membre

Re : Table temporaire dans une fonction

gleu a écrit :

Pas bizarre du tout. La table temporaire existe tout le temps de la session (donc tant qu'il n'y a pas déconnexion de l'éditeur de requête dans votre cas).

Je comprend, mais je ne me suis pas déconnecté?! Normalement elle aurait dûe exister encore puisque je n'ai pas fermer l'éditeur de requête, non? Le message que j'ai est comme si la table n'existait pas, alors qu'elle devrait logiquement exister encore si on prend en considération vos explications antérieures.

Merci beaucoup

Charles Morin
PostgreSQL 9.0.3 sur Windows 2008 Server Standard (x64)

Hors ligne

#22 18/08/2011 16:47:12

dverite
Membre

Re : Table temporaire dans une fonction

Ah! C'est pas LANGUAGE sql qu'il faut pour la fonction , c'est plpgsql.

@DanielVerite
http://blog-postgresql.verite.pro/

Hors ligne

#23 18/08/2011 16:49:10

charleydc5
Membre

Re : Table temporaire dans une fonction

dverite a écrit :

Ah! C'est pas LANGUAGE sql qu'il faut pour la fonction , c'est plpgsql.

Exactement!! J'ai changé le language et ajouté les BEGIN et END; et tout fonctionne à merveille...

Désolé les gars, c'était mon erreur.

Merci beaucoup pour votre support. C'est grandement apprécié.

Charles Morin
PostgreSQL 9.0.3 sur Windows 2008 Server Standard (x64)

Hors ligne

#24 18/08/2011 16:50:05

gleu
Administrateur

Re : Table temporaire dans une fonction

Normalement elle aurait dûe exister encore puisque je n'ai pas fermer l'éditeur de requête, non?

Hmmm, lisez le message d'erreur de pgAdmin : ERROR: relation "temptest" already exists. Il indique bien que la relation temptest existe déjà.

Et pour la fonction, dverite a raison. Vous voulez plpgsql et non pas sql.

Guillaume.

Hors ligne

#25 18/08/2011 16:51:39

charleydc5
Membre

Re : Table temporaire dans une fonction

Encore une fois j'avais lu trop vite! wink

Discussion close!

Charles Morin
PostgreSQL 9.0.3 sur Windows 2008 Server Standard (x64)

Hors ligne

Pages : 1 2

Pied de page des forums

Propulsé par FluxBB