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';
In PostgreSQL, we can drop a table using SQL SHELL and pgAdmin. Utilize the DROP TABLE command followed by the table name to drop the targeted table from SQL SHELL.
The first method to remove a PostgreSQL database is to use the following SQL statement: DROP DATABASE <database name>; The command removes the directory containing the database information and the catalog entries. Only the database owner can execute the DROP DATABASE command.
Open your pgAdmin and then go the object tree where we will go to the database and then move to the public section under schemas and then select the Employee table which we want to delete or drop. The drop table popup window will appear on the screen, where we will click on the Yes button to drop the Employee table.
DELETE deletes rows that satisfy the WHERE clause from the specified table. If the WHERE clause is absent, the effect is to delete all rows in the table. The result is a valid, but empty table.
DROP TABLE user_statistics;
DROP VIEW user_statistics;
complete syntax:
DROP TABLE
DROP VIEW
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With