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();

You are querying all the tables that start with `irm_gtresult`, not `irm`. Is that intentional? — Mureinik, Jun 16 '15 at 13:51
I pasted the code it is not a sceenshot, I judge upon size of the directory where the database resides. Yes, I want intentionally truncate those table that start with 'irm_gtresult'. In fact the standalone select table_name from information_schema.tables where table_name ILIKE 'irm_gtresult%' returns the list of tables exactly that I want to truncate. — d88, Jun 16 '15 at 14:04
You need to provide more information. Your version of Postgres? How exactly do you run it? Connected to the right database? Do you get any messages? If 'irm_gtresult%' is the pattern you want, please fix the text of your question. — Erwin Brandstetter, Jun 16 '15 at 14:05
Use row. table_name in your TRUNCATE statement, not just row. — Frank Heikens, Jun 16 '15 at 14:09
@FrankHeikens: `row` isn't actually a row in his code. That's just a very unfortunate variable name. — Erwin Brandstetter, Jun 16 '15 at 14:11
but the 'row' actually denotes the table name, look when I execute the following statement, I recieve the list of tables that I need to truncate: 53=# select table_name from information_schema.tables where table_name ILIKE 'irm_gtresult%' 53-# ; table_name ------------------------- irm_gtresult_1393803000 irm_gtresult_1393552800 — d88, Jun 16 '15 at 14:13
How do you know it's "not working"? How do you check that the tables are empty? — , Jun 16 '15 at 14:30

score 1 · Accepted Answer · edited May 23 '17 at 12:29

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

truncate function doesnt work in postgres

2 Answers2