Re: INSERT a number in a column based on other columns OLD INSERTs
От | Adrian Klaver |
---|---|
Тема | Re: INSERT a number in a column based on other columns OLD INSERTs |
Дата | |
Msg-id | 5585B33E.7080808@aklaver.com обсуждение исходный текст |
Ответ на | INSERT a number in a column based on other columns OLD INSERTs (litu16 <litumelendez@gmail.com>) |
Ответы |
Re: INSERT a number in a column based on other columns OLD INSERTs
(litu16 <litumelendez@gmail.com>)
|
Список | pgsql-general |
On 06/20/2015 10:44 AM, litu16 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; You need to use the assignment operator: http://www.postgresql.org/docs/9.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT so: t_ix := 1 > END IF; > END IF; > NEW.time_index = t_ix; Same here. > 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. > > > > -- > View this message in context: http://postgresql.nabble.com/INSERT-a-number-in-a-column-based-on-other-columns-OLD-INSERTs-tp5854577.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
В списке pgsql-general по дате отправления:
Предыдущее
От: litu16Дата:
Сообщение: INSERT a number in a column based on other columns OLD INSERTs
Следующее
От: Bill MoranДата:
Сообщение: Re: INSERT a number in a column based on other columns OLD INSERTs