Hello
I just made a short test with the code provided. As Bill mentioned the
moment when the trigger is fired is essential.
I made a test with both before (worked) and after (did not work because
the row was already inserted and the returned new row is ignored).
The assignment (= or :=) does not seem to play a role, but the correct
version is as mentioned :=
Bye
Charles
On 6/20/2015 21:37, Bill Moran wrote:
On Sat, 20 Jun 2015 10:44:21 -0700 (MST)
litu16 <litumelendez@xxxxxxxxx> wrote:
In PostgreSQL I have this table... (there is a primary key in the most left
side "timestamp02" which is not shown in this image)
in the table above, all columns are entered via querrys, except the
"time_index" which I would like to be filled automatically via a trigger
each time each row is filled.
This is the code to create the same table (without any value) so everyone
could create it using the Postgre SQL query panel.
*CREATE TABLE table_ebscb_spa_log02
(
pcnum smallint,
timestamp02 timestamp with time zone NOT NULL DEFAULT now(),
fn_name character varying,
"time" time without time zone,
time_elapse character varying,
time_type character varying,
time_index real,
CONSTRAINT table_ebscb_spa_log02_pkey PRIMARY KEY (timestamp02)
)
WITH (
OIDS=FALSE
);
ALTER TABLE table_ebscb_spa_log02
OWNER TO postgres;*
What I would like the trigger to do is:
INSERT a number in the "time_index" column based on the INSERTed values of
the "fn_name" and "time_type" columns in each row.
If both ("fn_name" and "time_type") do a combination (eg. Check Mails -
Start) that doesn't exist in any row before (above), then INSERT 1 in the
"time_index" column,
Elif both ("fn_name" and "time_type") do a combination that does exist in
some row before (above), then INSERT the number following the one
before(above) in the "time_index" column.
(pls look at the example table image, this trigger will produce every red
highlighted square on it)
I have tried so far this to create the function:
CREATE OR REPLACE FUNCTION on_ai_myTable() RETURNS TRIGGER AS $$
DECLARE
t_ix real;
n int;
BEGIN
IF NEW.time_type = 'Start' THEN
SELECT t.time_index FROM table_ebscb_spa_log02 t WHERE t.fn_name =
NEW.fn_name AND t.time_type = 'Start' ORDER BY t.timestamp02 DESC LIMIT 1
INTO t_ix;
GET DIAGNOSTICS n = ROW_COUNT;
IF (n = 0) THEN
t_ix = 1;
ELSE
t_ix = t_ix + 1;
END IF;
END IF;
NEW.time_index = t_ix;
return NEW;
END
$$
LANGUAGE plpgsql;
But when I manually insert the values in the table, nothing change (no error
message) time_index column just remain empty, what am I doing wrong???
Please some good PostgreSQL fellow programmer could give me a hand, I really
have come to a death point in this task, I have any more ideas.
Couple things.
First off, you don't show your statement for creating the trigger. This is important.
The trigger has to be a BEFORE trigger FOR EACH ROW. Otherwise, the returned value
won't do anything. It should read like this:
CREATE TRIGGER trigger_name
BEFORE INSERT ON table_ebscb_spa_log02
FOR EACH ROW EXECUTE PROCEDURE on_ai_myTable();
If you created it with AFTER INSERT or FOR EACH STATEMENT, then the trigger won't
work as desired.
The other thing about assignment being := was already mentioned.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
