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
| CREATE OR REPLACE FUNCTION "theriaque"."get_the_cipemg_spe_ter" (numeric, numeric, varchar) RETURNS "pg_catalog"."refcursor" AS
$body$
DECLARE
CodeId ALIAS FOR $1; TypId ALIAS FOR $2; LSTTER ALIAS FOR $3;
curRet REFCURSOR;
BEGIN
IF TypId = 1 THEN
OPEN curRet FOR EXECUTE
' SELECT t4.FCPMTER_FCPM_CODE_FK_PK AS IDCIPEMG,
t4.FCPMTER_NATURE_CIPEMG_PK AS NATURE,
t3.cdf_code_pk AS CODETER,
t3.cdf_nom AS TERRAIN,
t2.fcpmtx_texte AS TEXT,
t4.FCPMTER_NUMSEQ_PK AS NOSEC
FROM FCPMSP_CIPEMG_SPE t1
inner join FCPMTER_FCPM_TERRAIN t4 on t4.FCPMTER_FCPM_CODE_FK_PK = t1.FCPMSP_FCPM_CODE_FK_PK AND t4.FCPMTER_NATURE_CIPEMG_PK = ''C''
inner join CDF_CODIF t3 on t4.FCPMTER_CDF_TER_CODE_FK_PK = t3.CDF_CODE_PK AND t3.CDF_NUMERO_PK = ''CS''
left join FCPMTX_FICHECIPEMG_TEXTE t2 on t4.FCPMTER_FCPM_CODE_FK_PK = t2.FCPMTX_FCPM_CODE_FK_PK
AND t4.FCPMTER_CDF_TER_CODE_FK_PK = t2.FCPMTX_CDF_TER_CODE_FK_PK
AND t4.FCPMTER_NATURE_CIPEMG_PK = t2.FCPMTX_NATURECIPEMG_FK_PK
AND t4.FCPMTER_NUMSEQ_PK = t2.FCPMTX_NUMSEQ_TER_FK_PK
WHERE t1.FCPMSP_SP_CODE_FK_PK = '''||CodeId||'''
AND t4.FCPMTER_CDF_TER_CODE_FK_PK IN ('||LSTTER||')
ORDER BY 1 ';
END IF;
RETURN curRet;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100; |
Partager