trigger help
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
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'.
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
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
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
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?
__________________________________________________
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