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?
Advertisement
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:
Basics: