如何从PostgreSQL数据库中删除表*或视图? [英] How to delete table *or* view from PostgreSQL database?

问题描述

我在PostgreSQL数据库中有一个表或视图的名称，需要在单个pgSQL命令中删除.我怎么负担得起?

I have a name of table or view in PostgreSQL database and need to delete in in single pgSQL command. How can i afford it?

我能够选择表格系统表来查找是否存在任何具有这样名称但被程序部分卡住的表:

I was able to select form system table to find out if there any table with such a name but stuck with procedural part:

SELECT count(*) FROM pg_tables where tablename='user_statistics';

推荐答案

DROP TABLE user_statistics;

DROP VIEW user_statistics;

完整语法:

拖放表

拖放视图

如果您想要一个完整的功能，我尝试过这样的事情:

And if you want a complete function, i tried something like this:

CREATE OR REPLACE FUNCTION delete_table_or_view(objectName varchar) RETURNS integer AS $$
DECLARE
    isTable integer;
    isView integer;
BEGIN
    SELECT INTO isTable count(*) FROM pg_tables where tablename=objectName;
    SELECT INTO isView count(*) FROM pg_views where viewname=objectName;

    IF isTable = 1 THEN
        execute 'DROP TABLE ' || objectName;
        RETURN 1;
    END IF;

    IF isView = 1 THEN
        execute 'DROP VIEW ' || objectName;
        RETURN 2;
    END IF;

    RETURN 0;

END;
$$ LANGUAGE plpgsql;

这篇关于如何从PostgreSQL数据库中删除表*或视图?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！