truncate function doesnt work in postgres

Question

I have created the following function to truncate bunch of tables starting with "irm_gtresult". There are no syntax errors in my function, but the function doesn't truncate the tables when I run it. What could be wrong here?

My Postgres db version is 8.4.

create or replace function br()
RETURNS void
LANGUAGE plpgsql
AS
$$
DECLARE
row text;
BEGIN
 FOR row IN
select table_name from information_schema.tables where table_name ILIKE 'irm_gtresult%'
LOOP
EXECUTE 'TRUNCATE TABLE ' || row;
END LOOP;
END;
$$;

Call:

select br();

Answer 1

Your code is valid. I tested and it works for me in Postgres 9.4.
Using the outdated and unsupported version 8.4 (like you added) may be the problem. The version is just too old, consider upgrading to a current version.

However, I have a couple of suggestions:

• Don't use key word row as variable name.
• You don't need to loop, you can TRUNCATE all tables in a single command. Faster, shorter.
• You may need to add CASCADE if there are dependencies. Be aware of the effect.

CREATE OR REPLACE FUNCTION br()
  RETURNS void AS
$func$
BEGIN
   EXECUTE (
      SELECT 'TRUNCATE TABLE '
          || string_agg(format('%I.%I', schemaname, tablename), ',')
          || ' CASCADE'
      FROM   pg_tables t
      WHERE  tablename ILIKE 'irm_gtresult%'
      AND    schemaname = 'public'
      -- AND tableowner = 'postgres'  -- optionaly restrict to one user
      );
END
$func$  LANGUAGE plpgsql;

Call:

SELECT br();

I am using the view pg_tables from the system catalog. You can as well use information_schema.tables like you did. Note the subtle differences:

• How to check if a table exists in a given schema

Related answers with more explanation:

• Can I truncate tables dynamically?
• Truncating all tables in a Postgres database