Truncating table before dynamic SQL with looping variables inside of one function

Question

I have a function that loops through specific schema names and inserts data into a table. I would like to be able to truncate said table before the insert loop occurs. I've tried putting the truncate statement inside of the dynamic query and that caused it to only keep schema's data inside of the table. I also tried declaring it as it's own variable and then executing the statement separately from the looping statement -- but that resulted in the same.

So my question is -- Where exactly would I put a truncate table dwh.prod_table_notify statement within this function? So that every time I run this function the table would be truncated and then the insert would properly loop through each schema being returned from the FOR statement.

NOTE: I'm forced to use postgres 8.2

CREATE OR REPLACE FUNCTION dwh.dim_table_notification()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
Declare
       myschema varchar;
       sql2 text;                   
Begin 
for myschema in 
select distinct table_schema
from information_schema.tables
where table_name in ('dim_loan_type', 'dim_acct_type')
and table_schema NOT LIKE 'pg_%'
and table_schema NOT IN ('information_schema', 'ad_delivery', 'dwh', 'users', 'wand', 'ttd') 
order by table_schema
loop  
sql2 ='insert into dwh.prod_table_notify 
select '''|| myschema ||''' as userid, loan_type_id as acct_type_id, loan_type::varchar(10) as acct_type, loan_type_desc::varchar(50) as acct_type_desc, term_code, 1 as loan_type from '|| myschema || '.' ||'dim_loan_type where term_code is null
union
select '''|| myschema ||''' as userid, acct_type_id, acct_type::varchar(10), acct_type_desc::varchar(50), term_code, 0 as loan_type from '|| myschema || '.' ||'dim_acct_type where term_code is null';
execute sql2;
end loop;
END;
$function$

I think I'm missing something: why not just put the truncate first, before the loop? — Dmitri, Aug 07 '13 at 04:32
First statement: after `BEGIN`, and before `for myschema in`. Now that I look at it, you have your function declaration in there twice - is that just a copy/paste error? — Dmitri, Aug 07 '13 at 16:50

score 2 · Accepted Answer · answered Aug 11 '13 at 13:31

CREATE OR REPLACE FUNCTION dwh.dim_table_notification()
  RETURNS void LANGUAGE plpgsql AS
$func$
DECLARE
   myschema text;
BEGIN

-- truncate simply goes here:
TRUNCATE dwh.prod_table_notify;

FOR myschema IN
   SELECT quote_ident(table_schema)
   FROM   information_schema.tables
   WHERE  table_name IN ('dim_loan_type', 'dim_acct_type')
   AND    table_schema NOT LIKE 'pg_%'
   AND    table_schema NOT IN
          ('information_schema', 'ad_delivery', 'dwh', 'users', 'wand', 'ttd')
   ORDER  BY table_schema
LOOP
   EXECUTE '
   INSERT INTO dwh.prod_table_notify
              (userid, acct_type_id, acct_type, acct_type_desc, loan_type)
   SELECT '''|| myschema ||''', loan_type_id, loan_type::varchar(10)
        , loan_type_desc::varchar(50), term_code, 1 AS loan_type
   FROM   '|| myschema || '.dim_loan_type
   WHERE  term_code IS NULL
   UNION ALL
   SELECT '''|| myschema ||''' AS userid, acct_type_id, acct_type::varchar(10)
       , acct_type_desc::varchar(50), term_code, 0 AS loan_type
   FROM   '|| myschema || '.dim_acct_type
   WHERE term_code IS NULL';
END LOOP;
END
$func$

Are you sure, you can actually use TRUNCATE? Quoting the manual for 8.2:

TRUNCATE cannot be used on a table that has foreign-key references from other tables, unless all such tables are also truncated in the same command.

If tables are small, DELETE is faster than TRUNCATE to begin with:
DELETE FROM dwh.prod_table_notify;
You have to sanitize identifiers! Use quote_ident(), also available in pg 8.2.
No point in using DISTINCT here.
Provide a column definition list for your INSERT. Else it can break in confusing ways, when you change the table later.
If rows in the two legs of the SELECT are unique, use UNION ALL instead of UNION. No point in trying to fold duplicates.

Perfect, it works. And yes the table is small so I used DELETE FROM instead of truncate. You're a wonderful man Erwin! — precose, Aug 12 '13 at 17:12

Truncating table before dynamic SQL with looping variables inside of one function

1 Answers1