From: | Charles Clavadetscher <clavadetscher(at)swisspug(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: INSERT a number in a column based on other columns OLD INSERTs |
Date: | 2015-06-20 19:41:23 |
Message-ID: | 5585C1E3.6090700@swisspug.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)gmail(dot)com> 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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2015-06-20 19:48:58 | Re: INSERT a number in a column based on other columns OLD INSERTs |
Previous Message | Bill Moran | 2015-06-20 19:37:44 | Re: INSERT a number in a column based on other columns OLD INSERTs |