I have hundreds tables in a Postgresql-11 DB, need to be droped.
In order to simplified my work, I coded a function as following:
CREATE OR REPLACE FUNCTION dropTable( IN tab_name information_schema.sql_identifier, OUT succ bool ) LANGUAGE 'plpgsql' AS $func$ BEGIN EXECUTE format('DROP TABLE IF EXISTS %s CASCADE;', tab_name ); -- How to get the executing successfulness of above statement ? -- following line can NOT return a boolean result GET CURRENT DIAGNOSTICS msg = PG_CONTEXT; END $func$;
And then call it in a query like this:
SELECT dropTable("table_name") FROM information_schema."tables" WHERE table_schema = 'public' AND table_type = 'BASE TABLE' AND "table_name" LIKE 'ticks\___0000' ORDER BY "table_name";
It works indeed, but can NOT return a boolean result that indicates whether or not the drop action had been successfully executed.
Is there a way to do so?
Or I have to return a literal value i.e.