Pl/pgsql - Requête avec variable

Version imprimable

Bonjour,

Je débute avec le PL/SQl et je souhaiterais être éclairé sur un point, puis récupérer les valeurs d'une ligne d'une table temporaire pour exécuter une requête avec en paramètre certaines colonnes de cette table temporaire ?

Voici ce que j'ai commencé à faire :

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
CREATE OR REPLACE FUNCTION public.doublon(
	)
    RETURNS TABLE(res_period_id integer, geo_referential_item_name text, Nb_Com integer) 
    LANGUAGE 'plpgsql'
 
    COST 100
    VOLATILE 
    ROWS 1000
 
AS $BODY$
 
declare
 
doublon refcursor ;
 
begin
 
drop table if exists doublon ;
 
create table doublon (res_period_id integer, geo_referential_item_name text, Nb_Com integer);
insert into doublon (res_period_id , geo_referential_item_name , Nb_Com)
select res_period.id, geo_referential_item.name, count(geo_referential_item.id) as Nb_Com 
from sale_order
left join sale_order_item on sale_order_item.order_id = sale_order.id
left join geo_referential_item on sale_order_item.item_id = geo_referential_item.id
left join codeclient on sale_order.partner_id = codeclient.cc_id
left join regroupement on regroupement.res = codeclient.parent_id
left join res_period on res_period.id = sale_order.distrib_week_id
where regroupement.res in ('283','289','321','408','363')
and sale_order.state not in ('canceled','rejected')
and res_period.id > 105
--and geo_referential_item.name ='231044003'
and sale_order.name like 'V_DP%'
group by res_period.id, geo_referential_item.name 
having count(geo_referential_item.id) > 1
order by 3 desc ;
 
open doublon for select * from doublon ;
 
--
À partir de ce moment là, je souhaiterais effectuer une requête avec en paramètre  res_period_id et geo_referential_item_name
 
select *
from .......
where res_period_id = doublon.res_period_id
and geo_referential_item_name = doublon.geo_referential_item_name
 
return *
 
end;
$BODY$;
 
ALTER FUNCTION public.doublon()
OWNER TO pubaudit;

Bien à vous

1 pièce(s) jointe(s)

En passant,
Je pratiquais le PLSQL sous Oracle dans les années 90 et ça me rappelle des mauvais souvenirs car la syntaxe est sensible :calim2:
Voici ma contribution en PL/pgSQL si ça peut aider
Je prend l'unique ligne de doublon et je sélectionne l'unique ligne de matable (clé id = res_period_id)
Bien sûr on peut imaginer un cursor avec bien plus de lignes et un update à la place du select sur matable etc.

A Adapter bien sûr ...
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 drop table doublon ; create table doublon (res_period_id integer, geo_referential_item_name text, Nb_Com integer); drop table matable; create table matable (id integer, datum text); insert into doublon(res_period_id , geo_referential_item_name , Nb_Com ) values (1, 'xxxx',2); insert into matable(id ,datum ) values (1, 'yyyy'); select * from doublon; select * from matable; CREATE or replace FUNCTION test (v1 text) RETURNS text AS $$ declare doubloncur CURSOR for select * from doublon; d doublon%ROWTYPE; v_datum matable.datum%TYPE; begin open doubloncur; loop fetch doubloncur into d; exit when not found; RAISE notice 'd.res_period_id %',d.res_period_id; /* valable si on a un seul enregistrement à afficher sinon il faudra un cursor */ select m.datum from matable m into v_datum where m.id = d.res_period_id; /* valable si on a un seul enregistrement à afficher sinon il faudra un cursor */ RAISE notice 'v_datum %',v_datum; end loop; close doubloncur; return v_datum; end; $$ LANGUAGE plpgsql; select test('');
Pièce jointe 609562

type + fonction

Bonjour

la réponse est oui

j'ai toujours préféré faire comme cela

- création d'un type
- fonction

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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
 
 
-- Function: getAvoirList()
-- DROP FUNCTION getAvoirList(integer, character varying(4000), date, integer, integer, integer, integer, integer, varchar, varchar);
 
CREATE OR REPLACE FUNCTION getAvoirList(integer, character varying(4000), date, integer, integer, integer, integer, integer, varchar, varchar)
  RETURNS SETOF getAvoirList_t AS
$BODY$ 
-- -------------------------------------------------------------------------------------------
-- nom              getAvoirList
-- type             procedure PLSQL
-- auteur           MATTASOGLIO
-- version          1.0
-- variables
-- exemple
-- description     demandeAvoir
-- select des avoirs pour les restaurants
 
/*
 
drop type getAvoirList_t cascade;
 
create type getAvoirList_t as (
dys_id integer,
dys_date date,
s_rest_id integer,
s_rest_lib character varying(30),
s_fou_id integer,
s_fou_lib character varying(50),
dys_dav_num_av_fou character varying(30),
es_num_bl character varying(30),
es_num_fact character varying(30),
dys_dav_ht numeric(14,4) , -- total HT avoir
dys_dav_ttc numeric(14,4) , -- total TTC avoir
dys_dav boolean ,
dys_ex_compta boolean
);
 
*/
 
-- -------------------------------------------------------------------------------------------
DECLARE
 
v_s_cligrp_id ALIAS FOR $1;
v_rest_list ALIAS FOR $2;
v_date ALIAS FOR $3;
v_s_fou_id ALIAS FOR $4;
v_traite ALIAS FOR $5;
v_type ALIAS FOR $6;
v_limit ALIAS FOR $7;
v_offset ALIAS FOR $8;
v_order ALIAS FOR $9;
v_sort ALIAS FOR $10;
 
 
v_getAvoirList_t getAvoirList_t%ROWTYPE;
 
v_sql text;
 
v_vide char;
 
BEGIN
 
if (v_rest_list = '') THEN v_rest_list := '0'; END IF;
v_vide := '';
v_sql := '
SELECT
    r_dysfonctionnement.dys_id ,
    r_dysfonctionnement.dys_date ,
    s_restaurant.s_rest_id ,
    s_restaurant.s_rest_lib ,
    s_fournisseur.s_fou_id ,
    s_fournisseur.s_fou_lib ,
    r_dysfonctionnement.dys_dav_num_av_fou ,
    r_entree.es_num_bl ,
    r_entree.es_num_fact ,
    r_dysfonctionnement.dys_dav_ht ,
    r_dysfonctionnement.dys_dav_ttc ,
    r_dysfonctionnement.dys_dav,
    r_dysfonctionnement.dys_ex_compta
FROM r_dysfonctionnement, s_fournisseur, s_restaurant , r_entree
WHERE  r_entree.es_id =  r_dysfonctionnement.es_id
AND    r_entree.s_fou_id =  s_fournisseur.s_fou_id
AND    r_entree.s_rest_id =  s_restaurant.s_rest_id
AND    r_dysfonctionnement.statut = TRUE
AND    r_dysfonctionnement.s_cligrp_id = ' || v_s_cligrp_id || '
AND     s_restaurant.s_rest_id in ('||v_rest_list||')
';
 
    -------------------------------------------------------------------------
 
    IF (coalesce(v_s_fou_id, 0) > 0  )  THEN
        v_sql := v_sql || ' AND s_fournisseur.s_fou_id  =' || v_s_fou_id ||' ';
    END IF;
 
 
    IF (coalesce(v_date, '1970-01-01') !='1970-01-01'   )  THEN
        v_sql := v_sql || ' AND r_dysfonctionnement.dys_date >= ''' || v_date ||''' ';
    END IF;
 
    IF (coalesce(v_type, 0) > 0 ) THEN
 
	IF (v_type = 1) THEN  -- demande d'avoir
		v_sql := v_sql || ' AND r_dysfonctionnement.dys_dav = true ';
 
		IF (v_traite > 0) THEN
			IF (v_traite = 1) THEN  -- avoir traité
			     v_sql := v_sql || ' AND r_dysfonctionnement.dys_dav_num_av_fou != '''' ';
			END IF;
 
			IF (v_traite = 2) THEN  -- demande d'avoir non traité
			    v_sql := v_sql || ' AND coalesce(r_dysfonctionnement.dys_dav_num_av_fou, '''') = ''''   ';
			END IF;
		END IF;
	END IF;
 
	IF (v_type = 2) THEN -- dysfonctionnement
	    v_sql := v_sql || ' AND r_dysfonctionnement.dys_dav = false ';
	END IF;
    END IF;
 
 
    -- SI v_sort NULL ALORS trie par defaut
    IF (trim(coalesce(v_sort, '')) = ''  OR (upper(trim(coalesce(v_order, ''))) = '' )    )
    THEN
        v_sort := ' DESC';
        v_order := 'dys_date';
    END IF;
 
    IF v_order = 'dys_date'
    THEN v_sql := v_sql || ' ORDER BY  dys_date ' || v_sort; END IF;
    IF v_order = 's_rest_lib'
    THEN v_sql := v_sql || ' ORDER BY  s_rest_lib ' || v_sort; END IF;
    IF v_order = 's_fou_lib'
    THEN v_sql := v_sql || ' ORDER BY  s_fou_lib ' || v_sort; END IF;
 
 
    IF coalesce(v_limit, 0) > 0
    THEN v_sql := v_sql || ' LIMIT   ' || to_char(v_limit, '999999');
    ELSE v_sql := v_sql || ' LIMIT   20 '; END IF;
    IF coalesce(v_offset, 0) > 0
    THEN v_sql := v_sql || ' OFFSET  ' || to_char(v_offset, '00000');
    ELSE v_sql := v_sql || ' OFFSET   0 '; END IF;
 
 
--RAISE NOTICE '*%*',v_sql;
 
-- -- BOUCLE
FOR v_getAvoirList_t  IN EXECUTE  v_sql
    LOOP
        RETURN NEXT  v_getAvoirList_t ;
    END LOOP;
 
RETURN;
 
END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
 
 
 
ALTER FUNCTION getAvoirList(integer, character varying(4000), date, integer, integer, integer, integer, integer, varchar, varchar) OWNER TO postgres;
 
--select * from getAvoirList(2, 3, null, null, 0, 0, 20, 0, 'dys_date' , 'ASC');
--select * from getAvoirList(2, 3, null, null, null, null, '20', '0', 'dys_date', 'desc');
select * from getAvoirList(2, '1,2,3,6,7,8', null, null, 0, 0, 20, 0, 'dys_date' , 'DESC');
 
--

évidement il faut créer le type avant de compiler la fonction

Plus lourd mais plus clair plus propre

Cdlt