Truncate if exists in psql function and call function

I have the following code to create a function that truncates all rows from the table web_channel2 if the table is not empty:

create or replace function truncate_if_exists(tablename text)
returns void language plpgsql as $$
begin
    select
    from information_schema.tables 
    where table_name = tablename;
    if found then
        execute format('truncate %I', tablename);
    end if;
end $$;

Unfortunately I don’t know how should I continue … How to execute the function?

Answer

TLDR

To execute a Postgres function (returning void), call it with SELECT:

SELECT truncate_if_exists('web_channel2');

Proper solution

… how should I continue?

Delete the function again.

DROP FUNCTION truncate_if_exists(text);

It does not offer any way to schema-qualify the table. Using it might truncate the wrong table …

Looks like you are trying to avoid an exception if the table is not there.

And you only want to truncate …

if the table is not empty

To that end, I might use a safe function like this:

CREATE OR REPLACE FUNCTION public.truncate_if_exists(_table text, _schema text DEFAULT NULL)
  RETURNS text
  LANGUAGE plpgsql AS
$func$
DECLARE
   _qual_tbl text := concat_ws('.', quote_ident(_schema), quote_ident(_table));
   _row_found bool;
BEGIN
   IF to_regclass(_qual_tbl) IS NOT NULL THEN   -- table exists
      EXECUTE 'SELECT EXISTS (SELECT FROM ' || _qual_tbl || ')'
      INTO _row_found;

      IF _row_found THEN                        -- table is not empty
         EXECUTE 'TRUNCATE ' || _qual_tbl;
         RETURN 'Table truncated: ' || _qual_tbl;
      ELSE  -- optional!
         RETURN 'Table exists but is empty: ' || _qual_tbl;
      END IF;
   ELSE  -- optional!
      RETURN 'Table not found: ' || _qual_tbl;
   END IF;
END
$func$;

To execute, call it with SELECT:

SELECT truncate_if_exists('web_channel2');

If no schema is provided, the function falls back to traversing the search_path – like your original did. If that’s unreliable, or generally, to be safe (which seems prudent when truncating tables!) provide the schema explicitly:

SELECT truncate_if_exists('web_channel2', 'my_schema');

db<>fiddle here

When providing identifiers as strings, you need to use exact capitalization.

Why the custom variable _row_found instead of FOUND? See:

• Dynamic SQL (EXECUTE) as condition for IF statement

Basics:

• Table name as a PostgreSQL function parameter

• How to check if a table exists in a given schema

• PL/pgSQL checking if a row exists

• How does the search_path influence identifier resolution and the “current schema”

• Are PostgreSQL column names case-sensitive?