Postgres操作之函数

金鱼kk

于 2022-10-11 22:11:41 发布

阅读量80

点赞数

分类专栏： Postgres 文章标签： sql 数据库

本文链接：https://blog.csdn.net/weixin_44548228/article/details/127272803

版权

Postgres 专栏收录该内容

16 篇文章 1 订阅

订阅专栏

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;