sqlpostgresqlplpgsqltruncatesearch-path

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?

Solution

  • 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:

    Basics: