DROP FUNCTION IF EXISTS CalculateCatalogLevel(INID varchar) CASCADE ;
CREATE OR REPLACE FUNCTION CalculateCatalogLevel(INID varchar) returns INT AS
$$
DECLARE
catalogid VARCHAR;
parentid VARCHAR;
f_level INT;
BEGIN
f_level = 0;
select ylzd1 into parentid from b_spjk_spfzb where b_spjk_spfzb.yhz = INID;
if (parentid = INID) then
return f_level;
end if;
select yhz, ylzd1 into catalogid, parentid from b_spjk_spfzb where b_spjk_spfzb.yhz = parentid;
if (parentid = catalogid) then
f_level = f_level + 1;
return f_level;
else
f_level = f_level + 1;
end if;
select yhz, ylzd1 into catalogid, parentid from b_spjk_spfzb where b_spjk_spfzb.yhz = parentid;
while catalogid != parentid loop
f_level = f_level + 1;
select yhz, ylzd1 into catalogid, parentid from b_spjk_spfzb where b_spjk_spfzb.yhz = parentid;
end loop;
return f_level + 1;
END;
$$
LANGUAGE plpgsql;