Instruction EXECUTE avec paramètres

Version imprimable

14/06/2006, 13h57
@n@kin.be

Instruction EXECUTE avec paramètres
Quelqu'un peut-il m'expliquer où se situe mon erreur:

Ceci fonctionne:
Code:

1 2 3 SELECT INTO member_info * FROM t_tmp_member; RETURN member_info;
Cela ne fonctionne pas:
Code:

1 2 3 4 sql_select:='SELECT INTO ' || member_info || ' * FROM t_tmp_member;'; EXECUTE sql_select; RETURN member_info;
et génère ce type d'erreur:

Code:

ERROR: could not find array type for data type typ_member_alldetails

Un petit tour sur le Net ne m'a pas vraiment aidé :(

Une idée?

Merci d'avance!
15/06/2006, 08h35
gerald2545

je crois avoir vu dans la doc postgresql il n'y a pas longtemps que le SELECT INTO n'est pas supporté par la commande EXECUTE.
ce qui explique peut-être ton problème.
bonne journée
15/06/2006, 08h40
gerald2545

précision :
ta commande SELECT INTO ne te renverra que le dernier enregistrement récupéré par ta requête. Si tu veux tous les enregistrements retournés par ta requete, il faut boucler.
16/06/2006, 10h51
@n@kin.be

Citation:

je crois avoir vu dans la doc postgresql il n'y a pas longtemps que le SELECT INTO n'est pas supporté par la commande EXECUTE

Merci pour ta réponse Gerald, je n'avais pas vu ça dans la doc, ça serait bien en effet la cause de mon problème.

Citation:

ta commande SELECT INTO ne te renverra que le dernier enregistrement récupéré par ta requête

Aucun niveau à ce problème, elle ne doit retourner qu'un record :)

Je m'enfonce dans une impasse... En fait, pour "contourner" le problème rencontré et expliqué dans ce forum (sujet : Fonction: Retour d'un SET dynamique), j'ai trouvé une solution passant par une table temporaire et la déclaration d'un type. Cette solution fonctionne à merveille :) ... mais 1 seule fois :( car vu que je travaille avec un CREATE TEMP TABLE, je tombe dans le side effect de pl/pgsql qui me donne une erreur "relation with OID ###### does not exist" et pour laquelle on me recommande d'utiliser EXECUTE pour les accès à la table... si mon SELECT INTO ne peut figurer dans mon EXECUTE, je tourne en rond...

Je vais essayé de trouvé une solution pour contourner les problèmes rencontrés avec ma solution de contour ;)

Outils puissant mais avec quelques limites déplaisantes :(

Hello,

pourquoi un EXECUTE?
un simple :
Code:

1 2 3 4 5 6 7 DECLARE member_info RECORD; BEGIN SELECT INTO member_info * FROM t_tmp_member; .... END;
ca marche pas?

16/06/2006, 15h32
@n@kin.be

Si Spoutnik, cela fonctionne, mais comme je l'ai indiqué juste ici plus haut, cela ne fonctionne qu'une seule fois. Une fois la fonction exécutée, je dois la supprimer (drop) et la recréer pour que cela fonctionne à nouveau.

Voici ce que j'ai trouvé dans les FAQ sur postgresql.org:

Citation:

PL/PgSQL caches function scripts, and an unfortunate side effect is that if a PL/PgSQL function accesses a temporary table, and that table is later dropped and recreated, and the function called again, the function will fail because the cached function contents still point to the old temporary table. The solution is to use EXECUTE for temporary table access in PL/PgSQL. This will cause the query to be reparsed every time.

J'ai 5 instructions dans ma fonction: create temp table, insert, update, select into et le drop de ma table. Pour suivre les conseils de la FAQ, j'ai donc fais des EXECUTE mais seul le "select into" pose problème.
16/06/2006, 15h52
gerald2545

et es-tu obligé de travailler sur une table temporaire?
16/06/2006, 15h57
Spoutnik

Citation:

et es-tu obligé de travailler sur une table temporaire?

oui, c'est ca que je comprend pas non plus ....

@n@kin.be, tu pourrais mettre le code de ta fonction qu'on comprenne à quoi sert la table temporaire?

Non, je ne suis pas obligé de travailler sur une table temporaire mais que cela soit une table temporaire ou non, le résultat est le même.

Voici la première version (sans EXECUTE):

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
 
CREATE OR REPLACE FUNCTION f_member_info(int4) RETURNS typ_member_alldetails AS
 
$BODY$
DECLARE member_info typ_member_alldetails;
 
BEGIN
 
CREATE TABLE t_tmp_member(
    card_id int4,
    member_type char(2),
    name char(30),
    firstname char(30),
    street char(50),
    street_nb char(5),
    zip char(5),
    city char(50),
    phone char(15),
    gsm char(15),
    email char(50),
    -- EF info
    section char(1),
    office char(10),
    offphone char(15),
    title char(3),
    off_nb char(10),
    -- AD info
    civ_status char(1),
    cat_desc char(30),
    sponsor_id int4,
    -- AG info
    sponsor_1_id int4,
    sponsor_2_id int4);
 
INSERT INTO t_tmp_member
SELECT *
FROM v_members_details
WHERE v_members_details.card_id = $1;
 
UPDATE t_tmp_member
SET component = (SELECT component FROM v_ef_details WHERE card_id = $1),
    section = (SELECT section FROM v_ef_details WHERE card_id = $1),
    office = (SELECT office FROM v_ef_details WHERE card_id = $1),
    offphone = (SELECT offphone FROM v_ef_details WHERE card_id = $1),
    title = (SELECT title FROM v_ef_details WHERE card_id = $1),
    off_nb = (SELECT off_nb FROM v_ef_details WHERE card_id = $1),
    civ_status = (SELECT civ_status FROM v_ad_details WHERE card_id = $1),
    cat_desc = (SELECT cat_desc FROM v_ad_details WHERE card_id = $1),
    sponsor_id = (SELECT sponsor_id FROM v_ad_details WHERE card_id = $1),
    sponsor_1_id = (SELECT sponsor_1_id FROM v_ag_details WHERE card_id = $1),
    sponsor_2_id = (SELECT sponsor_2_id FROM v_ag_details WHERE card_id = $1)
WHERE card_id = $1;
 
SELECT * FROM t_tmp_member INTO member_info;
 
DROP TABLE t_tmp_member;
 
RETURN member_info;
 
END;
 
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

A la première exécution de

Code:

select * from f_member_info(1)

il me retourne un résultat correcte mais ensuite, il me retourne ceci:

Code:

1
2
3
4
 
ERROR:  relation with OID 18178 does not exist
CONTEXT:  SQL statement "INSERT INTO t_tmp_member SELECT * FROM v_members_details WHERE v_members_details.card_id =  $1 "
PL/pgSQL function "f_member_info" line 36 at SQL statement

Voilà, j'espère que ces informations vont vous aider à y voir plus clair et d'hors et déjà, je vous remercie de l'intérêt que vous portez à mon petit problème.

16/06/2006, 16h34
gerald2545

en regardant viteuf, tu fais

Code:

SET component = (SELECT component FROM v_ef_details WHERE card_id = $1),

mais le champs component n'existe pas dans ta table temporaire...je regarde plus en détail
16/06/2006, 16h41
gerald2545

je suis peut-être à côté de la plaque, mais pourquoi est-ce que tu ne fais pas des jointures plutôt que de travailler sur une table temporaire que tu mets à jour selon ce même principe?

Ok, je comprend un peu mieux.
Déjà, ce que tu utilise n'est pas une table temporaire au sens SQL (qu'on me corrige si je me trompe surtout!).
Tu as un mot clef (TEMP / TEMPORARY) à utiliser pour que ta table soit droppée automatiquement à la fin de la session.

La séquence CREATE table + insert pourrait peut déjà être remplacée par

Code:

SELECT INTO TEMP t_tmp_member

ou bien par l'utilisation d'un type record.

"member_info" sert à quoi?
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 CREATE OR REPLACE FUNCTION f_member_info(int4) RETURNS typ_member_alldetails AS $BODY$ DECLARE t_tmp_member RECORD; BEGIN SELECT members.card_id , members.member_type , members.name , members. firstname , members.street , members.street_nb , members.zip , members.city , members.phone , members.gsm , members.email , -- EF info offi.section, offi.office , offi.offphone , offi.title , offi.off_nb , -- AD info, offi.civ_status , offi.cat_desc , offi.sponsor_id , -- AG info offi.sponsor_1_id , offi.sponsor_2_id ); INTO t_tmp_member FROM v_members_details members join office offi on (members.card_id = offi.card_id) WHERE members.card_id = $1; RETURN t_tmp_member; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE;
Je ne suis pas qur que ca plante pas à la compilation, à cause du possible retour de plusieurs lignes.

A mois que tu ai un besoin précis de redéfinir les types de tes colonnes ( de t_tmp_member;) , ca sert à rien de le faire.

+1 pour le 'component' qui existe pas, j'avais po vu ;)

c'était en effet l'idée que je me faisais de la solution, sauf que tu as fait un petit mic mac dans les tables/vues ;)
la requête devant être (je crois) :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
SELECT 
members.card_id ,
members.member_type ,
members.name ,
members. firstname ,
members.street ,
members.street_nb ,
members.zip ,
members.city ,
members.phone ,
members.gsm ,
members.email ,
    -- EF info
    ef.section, 
    ef.office ,
    ef.offphone ,
    ef.title ,
    ef.off_nb ,
    -- AD info,
    ad.civ_status ,
    ad.cat_desc ,
    ad.sponsor_id ,
    -- AG info
    ag.sponsor_1_id ,
    ag.sponsor_2_id );
 INTO t_tmp_member
FROM members 
JOIN v_ef_details ef on (members.card_id = ef.card_id)
JOIN v_ad_details ad on (members.card_id = ad.card_id)
JOIN v_ag_details ag on (members.card_id = ag.card_id)
WHERE members.card_id = $1;

16/06/2006, 17h28
Spoutnik

:oops: j'ai lu trop vite les noms.:oops:
16/06/2006, 17h41
gerald2545

:mouarf: pas grave ;)d'ailleurs moi aussi je me suis planté dans les joins, j'ai mis le même alias pour les trois, je viens de corriger

ben non...

Merci encore à Gerald et à Spoutnik pour leur intérêt à résoudre mon problème.

L'idée est bonne est les "JOIN" mais cela ne peut fonctionner car un membre est soit EF (effectif), AD (adhérent) ou AG (agréé). Si j'écris ma fonction avec les joins pour membres, ef, ad et ag, la requête ne me retourne rien.

C'est la raison pour laquelle j'avais pensé à un insert suivit d'un update (pour le component, c'est juste une erreur de copier/coller ;) ) puisque comme je vous l'ai dit, ma fonction fonctionne 1 fois.

Au départ, ce que j'avais fais était plus simple
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 CREATE OR REPLACE FUNCTION f_member_info(int4) RETURNS ??? AS $BODY$ DECLARE memtype char(2); BEGIN SELECT member_type FROM t_members WHERE card_id=$1 INTO memtype; IF memtype = 'EF' THEN SELECT m.*, ef.* FROM v_members_details m, v_ef_details ef WHERE m.card_id=ef.card_id AND m.card_id=$1; ELSIF memtype = 'AD' THEN SELECT m.*, ad.* FROM v_members_details m, v_ad_details ef WHERE m.card_id=ad.card_id AND m.card_id=$1; ELSE SELECT m.*, ag.* FROM v_members_details m, v_ag_details ef WHERE m.card_id=ag.card_id AND m.card_id=$1; END IF; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE;
J'avais écris ça à sauce "MS SQL Server", SGDB sur lequel j'avais suivi toutes mes formations mais mon problème avec PostGreSQL c'est le type de retour à spécifier car mes trois SELECT ne me retourne pas la même chose.

On est lundi, on est motivé alors on va continuer à chercher ;)
Une solution par le front-end reste possible mais je préfère éviter, j'utiliserai cette solution en dernier recourd.

19/06/2006, 10h55
Spoutnik

Hello,

regarde du coté des anyelement

+SET OF si tu as plusieurs lignes à renvoyer.

Resolu

Ok Spoutnik, je jetterai un oeil sur les types polymorphiques!

Mais en attendant, en postant ma dernière réponse, une idée m'a traversé l'esprit (ça fait mal!) et j'ai alors décidé de combiné ma première solution avec vos idées (merci encore).
Voici en gros ce que cela donne:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
 
CREATE OR REPLACE FUNCTION f_select_member_details(int4)
  RETURNS typ_member_alldetails AS
$BODY$
 
DECLARE 
	memtype char(2);
	myrec typ_member_alldetails;
 
BEGIN
 
SELECT member_type FROM t_members WHERE card_id=$1 INTO memtype;
 
IF memtype = 'EF' THEN
	SELECT	members.card_id,
		members.member_type,
		members.name,
		members. ... (tous les champs de members),
		members.fct_code,
		ef. ... (tous les champs de la View EF),
	INTO myrec
	FROM v_members_details members
	JOIN v_ef_details ef on (members.card_id = ef.card_id)
	WHERE members.card_id = $1;
ELSIF memtype = 'AD' THEN
	SELECT	members.card_id,
		members.member_type,
		members.name,
		members. ... (tous les champs de members),
		members.fct_code,
                          null,
                          null,
                          null,
                          null,
                          null,
                          null, (autant de null que de champs dans la view EF)
		ad. ... (tous les champs de la View AD),
	INTO myrec
	FROM v_members_details members
	JOIN v_ad_details ef on (members.card_id = ad.card_id)
	WHERE members.card_id = $1;
ELSE
	SELECT	members.card_id,
		members.member_type,
		members.name,
		members. ... (tous les champs de members),
		members.fct_code,
                          null,
                          null,
                          null,
                          null,
                          null,
                          null, 
                          null,
                          null,
                          null, (autant de null que de champs dans les view EF et AD)
		ag. ... (tous les champs de la View AG),
	INTO myrec
	FROM v_members_details members
	JOIN v_ag_details ef on (members.card_id = ag.card_id)
	WHERE members.card_id = $1;
END IF;
 
RETURN myrec;
 
END;
 
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

Merci pour votre aide qui a été pour moi un source d'inspiration.