Triggers in pgadmin query tools

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;

Responses

Browse pgadmin-support by date

  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.