drop table if为空/将count(*)的结果传递给变量

wpx232ag  于 2021-08-09  发布在  Java
关注(0)|答案(2)|浏览(415)

我想检查一张table是否是空的,如果是,我想把它放下。我知道这个小函数本身似乎不是一个有用的东西,但是我有一个更长的函数,所以这只是主要部分。

CREATE OR REPLACE FUNCTION public.cl_tbl(t_name character varying)
RETURNS void AS
$BODY$
DECLARE
    rownum int;
BEGIN
    SELECT COUNT(*) INTO rownum FROM format('myschema.%I',t_name);
    IF rownum = 0 then
        EXECUTE format('DROP TABLE myschema.%I',t_name);
    END IF;
    RETURN;
END;
$BODY$
 LANGUAGE plpgsql VOLATILE

我的问题是,这条线

SELECT COUNT(*) INTO rownum FROM format('myschema.%I',t_name);

如果表为空,则不返回0,而是返回1作为所选内容的行数。

| count(bigint)
--------------------
 1  |       0

我也试过这个:

rownum := SELECT COUNT(*) FROM format('myschema.%I',t_name);

但结果是一样的。如何传递给定表的实际行数?

3npbholx

3npbholx1#

尝试使用执行:

CREATE OR REPLACE FUNCTION public.cl_tbl(t_name character varying)
RETURNS void AS
$BODY$
DECLARE
    rownum int;
BEGIN
    EXECUTE format('select count(*) from %I', t_name) into rownum;
    IF rownum = 0 then
        EXECUTE format('DROP TABLE %I',t_name);
    END IF;
    RETURN;
END;
$BODY$
 LANGUAGE plpgsql VOLATILE
;
ycggw6v2

ycggw6v22#

您可以使用exists()- SELECT EXISTS(SELECT * FROM table_name) .

CREATE OR REPLACE FUNCTION public.cl_tbl(t_name character varying)
RETURNS void AS
$BODY$
DECLARE
    x BOOLEAN;
BEGIN
    EXECUTE format('select exists (select * from myschema.%I) t', t_name) INTO x;
    IF x = False then
        EXECUTE format('DROP TABLE myschema.%I',t_name);
    END IF;
    RETURN;
END;
$BODY$
 LANGUAGE plpgsql VOLATILE

相关问题