Forums PostgreSQL.fr

charleydc5

Membre

Accès aux paramètres d'une fonction à l'intérieur d'un PREPARE

Bonjour à tous,

J'ai une fonction qui contient une table temporaire, donc un "PREPARE" suivi d'un SELECT et de la commande CREATE TEMP TABLE.

J'avais le message suivant à l'exécution:

ERROR:  could not determine data type of parameter $2
CONTEXT:  SQL statement "PREPARE tmpCas AS

J'ai donc découvert qu'il fallait spécifier les types de paramètre dans une instruction PREPARE, car autrement PostgreSQL les perçoit comme "unknown".

Mon instruction PREPARE est donc passée de

PREPARE tmpCas AS

à

PREPARE tmpCas(integer, character varying, character varying, integer, character varying, integer, character varying, character varying, integer, integer, character varying) AS

Maintenant, mon erreur n'est plus la même. C'est maintenant :

ERROR:  operator does not exist: timestamp without time zone >= character varying
LINE 71:     stirq.Cheminement.datCheminement BETWEEN $3 AND $4 AND

Lorsque j'arrive à la ligne en question, je remarque PostgreSQL perçoit $3 et $4 comme le troisième et le quatrième paramètre de mon PREPARE (donc character varying et integer), alors que ma fonction a comme troisième et quatrième paramètre des timestamp (intervalle de date pour ma requête).

Lorsque je ne suis pas dans un prepare, c'est parfait. Par contre, dans un PREPARE, je n'arrive plus à accéder aux paramètres de ma fonction.

J'ai essayé d'ajouter ceci au début de ma fonction :

DECLARE
     ldatfrom TIMESTAMP := $3;
     ldatto TIMESTAMP := $4;

.. et d'utiliser ldatfrom et ldatto dans le WHERE du PREPARE... négatif.

Quelqu'un a une idée?

Finalement, voici le code complet de ma fonction:

CREATE OR REPLACE FUNCTION stirq.qrycastempscoutstyperpt(
pvintregionid INT, 
pvstrregionnom VARCHAR, 
pvdatfrom timestamp without time zone, 
pvdatto timestamp without time zone, 
pvstrlangue VARCHAR)

  RETURNS TABLE(intcasid integer, strnodossier character varying, strconseillernom character varying, intlocalisationid integer, strlocalisationnom character varying, intregionid integer, strregionnom character varying, stractivitenom character varying, inttemps integer, intcouts integer, strdossieretatnom character varying) AS
$BODY$

/*
DECLARE
	ldatfrom TIMESTAMP := $3;
	ldatto TIMESTAMP := $4;
*/

BEGIN

	PREPARE tmpCas(integer, character varying, character varying, integer, character varying, integer, character varying, character varying, integer, integer, character varying) 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 tmpCas AS EXECUTE tmpCas;
	
	INSERT INTO tmpCas
	SELECT	0,
		NULL,
		NULL,
		0,
		NULL,
		$1,
		$2::VARCHAR,
		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;
   
	RETURN QUERY SELECT * FROM tmpCas;

	DEALLOCATE tmpCas;

END;
	
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION stirq.qrycastempscoutstyperpt(integer, character varying, timestamp without time zone, timestamp without time zone, character varying) OWNER TO cxm526;

Merci !

---

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

gleu

Administrateur

Re : Accès aux paramètres d'une fonction à l'intérieur d'un PREPARE

Peut-être qu'on peut résoudre ça avec une requête dynamique. Difficile de savoir sans connaître l'erreur.

---

Guillaume.

dverite

Membre

Re : Accès aux paramètres d'une fonction à l'intérieur d'un PREPARE

Tu auras plus vite fait de supprimer le PREPARE qui n'est pas utile, dans le sens où tu peux faire:
CREATE TEMP TABLE tmpCas AS SELECT....
Le PREPARE est surtout utile quand une requête est réutilisée de multiples fois ce qui n'est pas le cas ici.

---

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

charleydc5

Membre

Re : Accès aux paramètres d'une fonction à l'intérieur d'un PREPARE

Ça fonctionne!

C'est bon à savoir que PREPARE n'est pas obligatoire pour les tables temporaires ponctuelles comme j'ai eu besoin de faire.

Merci beaucoup dverite pour les explications

---

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