Christopher B. Browne's Home Page
cbbrowne@acm.org

8.83. preparetableforcopy(integer)

Function Properties

PLPGSQLinteger
Delete all data and suppress index maintenance
    declare
    	p_tab_id		alias for $1;
    	v_tab_oid		oid;
    	v_tab_fqname	text;
    begin
    	-- ----
    	-- Get the OID and fully qualified name for the table
    	-- ---
    	select	PGC.oid,
    			slon_quote_brute(PGN.nspname) || '.' ||
    			slon_quote_brute(PGC.relname) as tab_fqname
    		into v_tab_oid, v_tab_fqname
    			from sl_table T,   
    				"pg_catalog".pg_class PGC, "pg_catalog".pg_namespace PGN
    				where T.tab_id = p_tab_id
    				and T.tab_reloid = PGC.oid
    				and PGC.relnamespace = PGN.oid;
    	if not found then
    		raise exception 'Table with ID % not found in sl_table', p_tab_id;
    	end if;
    
    	-- ----
    	-- Try using truncate to empty the table and fallback to
    	-- delete on error.
    	-- ----
    	perform TruncateOnlyTable(v_tab_fqname);
    	raise notice 'truncate of % succeeded', v_tab_fqname;
    
    	-- suppress index activity
            perform disable_indexes_on_table(v_tab_oid);
    
    	return 1;
    	exception when others then
    		raise notice 'truncate of % failed - doing delete', v_tab_fqname;
    		perform disable_indexes_on_table(v_tab_oid);
    		execute 'delete from only ' || slon_quote_input(v_tab_fqname);
    		return 0;
    end;

Google
Contact me at cbbrowne@acm.org