Postgresql – Postgres : Truncate if exists in psql function with parameter

functionspostgresql

I am trying to get a psql function that would truncate a given table name if it exists. I've been trying multiple functions, but none of them worked so far. Here is the code :

CREATE OR REPLACE FUNCTION truncateIfExists(tableName TEXT)
returns void
as $$
BEGIN
EXECUTE format(
'IF EXISTS (
    SELECT *
    FROM information_schema.tables 
    WHERE table_name =' || tableName || '
    )
THEN
TRUNCATE tableName;
END IF;
');
END;
$$language plpgsql

Now, I am able to get it to work in a simple procedure with a harcoded name :

do $$
begin
IF EXISTS (SELECT * 
 FROM information_schema.tables 
 WHERE table_name = genre_epf)
 THEN
 TRUNCATE genre_epf;
END IF;
end
$$;

But I can't wrap my head on how to mix both queries. What am I doing wrong here ?

Best Answer

Use the variable FOUND:

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

Note that I've used PERFORM instead of SELECT as I don't need an output of the query. I want to know whether the query returns any row (FOUND = true) or not (FOUND = false).

Related Question