Truncate if exists in psql function and call function

Question

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?

Spatz

what is the problem? – Stoa

I run the code, and I don't know what is the next step. How can I call the function with the right parameter. I get the message: Function truncate_if_exists created Execution time: 0.03s – Virnelli

If I write truncate_if_exists(web_channel2) I get syntax error – Virnelli

Your code doesn't truncate "if the table is not empty". It truncates if the table exists - those are different things. – Fantoccini

Answer 1

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?

Extinctive