11

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 ?

1
  • The problem is the TRUNCATE tableName;. You are trying to truncate a table named tableName, Nov 4 '17 at 20:16
9

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).

2
  • Thank you for your help. Is the underscore naming a Postgres convention ? Nov 5 '17 at 15:41
  • 1
    It's possible to use "camelCaseIdentifiers" but they must be enclosed in double-quotes to be really case-sensitive. Hence identifiers_with_underscores is a convention preferred by many advanced Postgres users. Read about identifiers in the documentation.
    – klin
    Nov 5 '17 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.