Thread: trigger help

trigger help

From

marcelo Cortez

Date:

22 August 2006, 13:38:39

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

Re: trigger help

From

"Christopher Browne"

Date:

22 August 2006, 13:55:26

On 8/22/06, marcelo Cortez <jmdc_marcelo@yahoo.com.ar> wrote:
>  I think my trigger need transaction ,but the pgsql
> compiler refuse to compile 'begin .. commit ' sequence
> I use the perform , to do the works

Stored functions already execute inside the context of some
already-running transaction.  You don't run multiple transactions
inside a function.

Further, when this is a trigger function, there is a very clear
transaction with which the activity of the trigger is already
associated.  If you were to start another transaction at the point
where you try to do so, that would break the processing of any further
triggers that might run after this one...
--
http://www3.sympatico.ca/cbbrowne/linux.html
Oddly enough, this is completely standard behaviour for shells. This
is a roundabout way of saying `don't use combined chains of `&&'s and
`||'s unless you think Gödel's theorem is for sissies'.

Re: trigger help

From

Michael Fuhr

Date:

22 August 2006, 13:59:49

On Tue, Aug 22, 2006 at 10:38:31AM -0300, marcelo Cortez wrote:
>  I think my trigger need transaction ,but the pgsql
> compiler refuse to compile 'begin .. commit ' sequence
> I use the perform , to do the works

Functions can't start or end transactions because they're already
being executed in the context of a transaction; you'll have to do
the BEGIN and COMMIT outside the function.  However, there is a way
around that restriction: the function could use dblink to connect
to the database as a client and then execute statements over that
connection.

>  CREATE OR REPLACE FUNCTION xxxx_create_cache(text)
>   RETURNS text AS

This isn't a trigger function.  Are you sure "trigger" is the
word you meant?

--
Michael Fuhr

Re: trigger help

From

marcelo Cortez

Date:

22 August 2006, 17:37:27

Micheal


> This isn't a trigger function.  Are you sure
> "trigger" is the
> word you meant?

 yes i do



CREATE TABLE actlocat
(
  id_actlocal numeric(2) NOT NULL,
  d_actlocal char(8) NOT NULL,
  f_novedad float8 NOT NULL,
  ordenado_por char(18) NOT NULL,
  CONSTRAINT pk_actlocat PRIMARY KEY (id_actlocal)
)
WITHOUT OIDS;
ALTER TABLE actlocat OWNER TO postgres;

CREATE TRIGGER sume_create_cache_actlocat_trigger
  AFTER UPDATE
  ON actlocat
  FOR EACH ROW
  EXECUTE PROCEDURE
xxxx_create_cache_actlocat_trigger_function();

.
.
.


CREATE OR REPLACE FUNCTION
xxxx_create_cache_actlocat_trigger_function()
  RETURNS "trigger" AS
$BODY$
  BEGIN
    PERFORM
xxxx_create_cache(lower(NEW.d_actlocal)::text);
    RAISE NOTICE
'xxxx_create_cache_actlocat_trigger_function(%)',
lower(NEW.d_actlocal)::text;
        RETURN NULL;
  END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION s

best regards
 MDC





__________________________________________________
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

Re: trigger help

From

Michael Fuhr

Date:

23 August 2006, 00:25:37

On Tue, Aug 22, 2006 at 02:37:19PM -0300, marcelo Cortez wrote:
> > This isn't a trigger function.  Are you sure "trigger" is the
> > word you meant?
>
>  yes i do

I see: the function you originally posted is called by a trigger
function.  In any case the answer is the same: functions can't start
or end transactions because they're already being executed in the
context of an outer transaction.  My previous message mentioned
using dblink as a way around that, but that's not necessarily good
design -- one problem is that if the outer transaction rolls back
then transactions that have already been committed over a dblink
connection won't be rolled back.  Doing transaction control from
outside the functions would probably be better.

--
Michael Fuhr

Re: trigger help

From

marcelo Cortez

Date:

23 August 2006, 10:51:58

Michael ,list

 You are you are right, thanks a lot for your help and
tinme.
 best regards
 MDC

 --- Michael Fuhr <mike@fuhr.org> escribió:

> On Tue, Aug 22, 2006 at 02:37:19PM -0300, marcelo
> Cortez wrote:
> > > This isn't a trigger function.  Are you sure
> "trigger" is the
> > > word you meant?
> >
> >  yes i do
>
> I see: the function you originally posted is called
> by a trigger
> function.  In any case the answer is the same:
> functions can't start
> or end transactions because they're already being
> executed in the
> context of an outer transaction.  My previous
> message mentioned
> using dblink as a way around that, but that's not
> necessarily good
> design -- one problem is that if the outer
> transaction rolls back
> then transactions that have already been committed
> over a dblink
> connection won't be rolled back.  Doing transaction
> control from
> outside the functions would probably be better.
>
> --
> Michael Fuhr
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>






__________________________________________________
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