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
|
CREATE OR REPLACE FUNCTION fctclassement(chpnumplu text, chpnumrayon text, chpca text, chpdatecumul text, chpmois text, chpannee text, nomtable text, numplurech int4, numrayrech int4, moisrech int4, anneerech int4, daterech text)
RETURNS int4 AS
$BODY$
DECLARE
cpt record;
rangcour integer;
txtreq text;
BEGIN
txtreq := 'SELECT ' ||chpnumplu|| ' as "numplu" FROM ' ||nomtable|| ' WHERE ' ||chpnumrayon|| ' = ' ||numrayrech||
IF chpdatecumul <> '' THEN
' and ' ||chpdatecumul|| ' = ' ||daterech||
ELSE
IF chpmois <> '' THEN
' and ' ||chpmois|| ' = ' ||moisrech||
END IF;
' and ' ||chpannee|| ' = ' ||anneerech||
END IF;
' ORDER BY ' ||chpca|| ' DESC;';
rangcour := 1;
FOR cpt IN EXECUTE txtreq LOOP
IF cpt.numplu = numplurech THEN
EXIT;
END IF;
rangcour := rangcour + 1;
END LOOP;
RETURN rangcour;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION fctclassement(chpnumplu text, chpnumrayon text, chpca text, chpdatecumul text, chpmois text, chpannee text, nomtable text, numplurech int4, numrayrech int4, moisrech int4, anneerech int4, daterech text) OWNER TO postgres; |
Partager