From: | Francois Legrand <legrand(at)lpnhe(dot)in2p3(dot)fr> |
---|---|
To: | pgadmin-support(at)postgresql(dot)org |
Subject: | Triggers in pgadmin query tools |
Date: | 2011-03-25 16:54:49 |
Message-ID: | 4D8CC8D9.2000103@lpnhe.in2p3.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgadmin-support |
Hi all,
I have a table with a trigger. When I run an insert command from psql,
the trigger is correctly fired, but if i run exactly the same command
from the pgqdmin3 query tool, the trigger is not fired !
Is that normal ?
Any clue ?
F.
---------------------------------------------------------------------------------------------------------------
PS : Here are the definitions :
---------------------------------------------------------------------------------------------------------------
CREATE TABLE databases
(
id_databases serial NOT NULL,
hostname character varying,
hostid character varying,
dbname character varying,
comments character varying,
CONSTRAINT databases_pkey PRIMARY KEY (id_databases),
CONSTRAINT db_is_unique UNIQUE (hostname, hostid, dbname)
)
WITH (
OIDS=TRUE
);
---------------------------------------------------------------------------------------------------------------
CREATE TRIGGER tai_databases0
AFTER INSERT OR UPDATE OR DELETE
ON databases
FOR EACH ROW
EXECUTE PROCEDURE update_sequences();
---------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION update_sequences()
RETURNS trigger AS
$BODY$
DECLARE
id_db_ int8 ;
pos int8;
shift_ int8 ;
min_ int8;
max_ int8;
nextval_ int8;
newval_ int8;
seq varchar ='';
sequences_ varchar[] ;
BEGIN
Select ARRAY['dbimage', 'exposure_file',
'exposure','dead','bias','flat','photflat','night','detrendset','subtraction',
'reference','release','access_mode','data_store','scheduler','object_type','log','code','action']
INTO sequences_;
SELECT * FROM get_id_db() INTO id_db_ ;
shift_:=10^12;
min_:=shift_*id_db_;
max_:=shift_*(id_db_+1)-1;
FOR pos IN 1 .. array_upper(sequences_, 1) LOOP
seq=sequences_[pos]||'_id_'||sequences_[pos]||'_seq';
SELECT nextval(seq) INTO nextval_ ;
IF ((nextval_ > max_) OR (nextval_ < min_)) THEN
SELECT setval(seq,min_,'true') INTO newval_;
END IF;
END LOOP;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
COST 100;
---------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION get_id_db()
RETURNS bigint AS
$BODY$
DECLARE
id_db_ int8 ;
BEGIN
id_db_:=0;
SELECT id_databases INTO id_db_
FROM databases
WHERE databases.hostname=hostname()
AND databases.hostid=hostid()
AND databases.dbname=current_database() ;
IF id_db_ IS NULL THEN
id_db_:=0;
END IF;
RETURN id_db_;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE SECURITY DEFINER
COST 100;
---------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION hostid()
RETURNS character varying AS
$BODY$
#!/bin/sh
hostid
$BODY$
LANGUAGE 'plsh' IMMUTABLE SECURITY DEFINER
COST 100;
---------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION hostname()
RETURNS character varying AS
$BODY$
#!/bin/sh
hostname
$BODY$
LANGUAGE 'plsh' IMMUTABLE SECURITY DEFINER
COST 100;
From | Date | Subject | |
---|---|---|---|
Next Message | Francois Legrand | 2011-03-25 17:07:53 | Triggers in pgadmin query tools |
Previous Message | Guillaume Lelarge | 2011-03-25 09:46:48 | Re: Polish language localisation. |