Inserted tables, deleted in PostgreSQL, like SQL Server?

advertisements

I want to create a trigger after a inserted event, but I need the data that I inserted in order to register into a new table for my trigger in PostgreSQL

In SQL Server I capture these values from the Inserted or deleted pseudo tables but do these tables also exists in PostgreSQL? Or what can I do?

This is my trigger code

CREATE TRIGGER tri_compago
  AFTER INSERT
  ON matricula
  FOR EACH ROW
  EXECUTE PROCEDURE fn_insCompPago();

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

   insert into compromisopago(codigotasa,descripcion,precio,fechavencimiento,codigomatricula)
   select codigotasa,descripcion,precio,fechavencimiento,i.codigo
   from programacionpago pp join inserted i on isnull(i.codigoconvenio,0) = isnull (pp.codigoconvenio,0)
   and pp.codigopresentacion = i.codigopresentacion
   where pp.vigencia = 1 and i.vigencia = 1;

   RETURN NULL;
  END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION fn_insCompPago()
  OWNER TO postgres;


I have no idea how triggers work in SQL Server but in PostgreSQL, you use the OLD and NEW special variables:

NEW
Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is NULL in statement-level triggers and for DELETE operations.

OLD
Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is NULL in statement-level triggers and for INSERT operations.

So you probably want to look at NEW.codigo, NEW.codigoconvenio, NEW.codigopresentacion, and NEW.vigencia in your case. You'd probably replace the i.vigencia = 1 part of the WHERE clause with a simple IF i.vigencia = 1 conditional as well.