patternsqlModerate
Postgres : Truncate if exists in psql function with parameter
Viewed 0 times
postgreswithfunctiontruncatepsqlexistsparameter
Problem
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 :
Now, I am able to get it to work in a simple procedure with a harcoded name :
But I can't wrap my head on how to mix both queries. What am I doing wrong here ?
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 plpgsqlNow, 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 ?
Solution
Use the variable FOUND:
Note that I've used
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).Code Snippets
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 $$;Context
StackExchange Database Administrators Q#190112, answer score: 12
Revisions (0)
No revisions yet.