trigger help

[marcelo Cortez <jmdc_marcelo@xxxxxxxxxxxx>]

folks


 I think my trigger need transaction ,but the pgsql
compiler refuse to compile 'begin .. commit ' sequence
I use the perform , to do the works

 i'm wrong?
 tia.
 any help be appreciated.
 MDC 

code below ( note  (*) for perform instruction) 




 CREATE OR REPLACE FUNCTION xxxx_create_cache(text)
  RETURNS text AS
$BODY$
  DECLARE
    tbl_name ALIAS FOR $1;
    
    v_record RECORD;

    v_mergefields TEXT;
    v_concatenator TEXT;
    v_cache TEXT;
    v_order TEXT;
    v_sql TEXT;

    v_array TEXT[] = '{}';
    v_field TEXT;    
 
  BEGIN
    v_concatenator = '';
    v_mergefields = '';
  
 
    FOR v_record IN select a.attname as attname,
t.typname = 'date' or t.typname = 'timestamp' as
isdate from pg_class as c, pg_attribute as a, pg_type
as t where c.oid = a.attrelid and a.atttypid = t.oid
and c.relname = tbl_name and a.attstattarget != 0
order by a.attnum LOOP
      v_field = v_record.attname;
      IF v_record.isdate = true THEN
        v_field = 'to_char(' || v_record.attname || ',
''dd/mm/yyyy'')';
      END IF;
      v_mergefields = v_mergefields || v_concatenator
|| v_field;
      v_concatenator = ' || chr(1) || ';
    END LOOP;
    
    v_cache = '';
    v_concatenator = '';
    v_order = '';
    
    SELECT INTO v_order ordenado_por FROM actlocat
WHERE d_actlocal = tbl_name;

    v_sql = 'SELECT ' || v_mergefields || ' as
row_cache FROM ' || tbl_name || ' ORDER BY ' ||
v_order;

    IF NOT FOUND THEN
      v_sql = 'SELECT ' || v_mergefields || ' as
row_cache FROM ' || tbl_name;
    END IF;

    FOR v_record IN EXECUTE v_sql LOOP
      v_array = array_append(v_array,
v_record.row_cache);
      --v_cache = v_cache || v_concatenator ||
v_record.row_cache;
      --v_concatenator = chr(255);
    END LOOP;

    v_cache = array_to_string(v_array, chr(255));
   (*)  PERFORM ' BEGIN ;' ;
    DELETE FROM table_cache WHERE table_name =
tbl_name;
    INSERT INTO table_cache (table_name, table_cache)
VALUES (tbl_name, v_cache);
   (*)  PERFORM ' COMMIT  ;' ;	
    RETURN v_cache;
    	
  END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
GRANT EXECUTE ON FUNCTION sume_create_cache(text) TO
public;




	
	
		
__________________________________________________
Preguntá. Respondé. Descubrí.
Todo lo que querías saber, y lo que ni imaginabas,
está en Yahoo! Respuestas (Beta).
¡Probalo ya! 
http://www.yahoo.com.ar/respuestas