How to return DDL success in a plpgsql function?

Question

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. RETURN True;?

Answer 1

A drop table cannot return a failed status. The command was either executed or an error was raised. In the latter case, the execution of the function and the surrounding transaction are aborted unless you trap the error.

---

psql can do the trick:

SELECT format('drop table %I', 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";
\gexec

the \gexec command will execute any SQL that was generated by the previous query.