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; |
Partager