Vous n'êtes pas identifié(e).
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
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
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
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
Pas d'erreur à l'exécution du PREPARE et du CREATE TEMP TABLE ? et qu'y a-t-il dans le [...] ?
Guillaume.
Hors ligne
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
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
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
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
É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
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
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
Les tables temporaires sont des schémas à part, donc non, ça ne peut pas être le problème.
Guillaume.
Hors ligne
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
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
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
Merci de présenter la fonction entière ainsi que le message d'erreur exact.
Guillaume.
Hors ligne
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
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
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
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
Ah! C'est pas LANGUAGE sql qu'il faut pour la fonction , c'est plpgsql.
@DanielVerite
http://blog-postgresql.verite.pro/
Hors ligne
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
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
Encore une fois j'avais lu trop vite! 
Discussion close!
Charles Morin
PostgreSQL 9.0.3 sur Windows 2008 Server Standard (x64)
Hors ligne