Re: trigger and returning the #of rows affected (partitioning)

["Ow Mun Heng" <ow.mun.heng@xxxxxxx>]

-----Original Message-----
> From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- 
> Is there any way in which a trigger can return the # of rows affected by 
> the
> insert / delete ?
>
> Master
> ---> slave_1
> ---> slave_2
>
> Trigger is against master which will, based on the conditions re-direct 
> the
> data into the relevant slave_X partitions.
>
> I think this post basically is what I am seeing. 
> http://www.nabble.com/affected-rows-count-td21124000.html
>
> If there any methods in which I can get back the affected # or rows
> affected? Or will I have to use / do another trigger on the slave_X tables
> to do the counts? 


I tried something like this, but it doesn't seem to be working. The Update
is not updating. Does this have anything to do with me returning NULL?

CREATE OR REPLACE FUNCTION test_insert_trigger() RETURNS trigger AS
$BODY$ 
BEGIN

IF (NEW.code in ('PROD') THEN 
  BEGIN 
    INSERT INTO test_prod VALUES (NEW.*);
      UPDATE xmms.log_update 
         SET rows_inserted = rows_inserted + 1 
       WHERE job_name = 'mssql_2_pg' 
         AND table_name = 'test' 
         AND from_date = (SELECT last_sync FROM xmms.log_sync WHERE
table_name = 'test' AND db_name = 'test_tbl');

  END;
ELSEIF NEW.code not in ('PROD') THEN 
  BEGIN 
    INSERT INTO test_eval VALUES (NEW.*);
      UPDATE xmms.log_update 
         SET rows_inserted = rows_inserted + 1 
       WHERE job_name = 'mssql_2_pg' 
         AND table_name = 'test' 
         AND from_date = (SELECT last_sync FROM xmms.log_sync WHERE
table_name = 'test' AND db_name = 'test_tbl');
  END;
ELSE   
    INSERT INTO test_orphan VALUES (NEW.*);    
RAISE NOTICE 'ORPHAN Found. Fix the function!';   
  
END IF;
    RETURN NULL;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100;

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general