On 06/23/2015 11:20 PM, litu16 wrote:
In PostgreSQL I have this table... (there is a primary key serial column in the most left side "stmtserial" which is not shown in this image) <http://postgresql.nabble.com/file/n5854916/screenshot.jpg> in the table above, all columns are entered via querrys, except the "time_index" which I is automatically filled via a PER-ROW trigger. 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_log04 ( pcnum smallint, stmtserial integer NOT NULL DEFAULT nextval('table_ebscb_spa_log04_stmtnum_seq'::regclass), fn_name character varying, "time" timestamp without time zone, time_elapse character varying, time_type character varying, time_index real, CONSTRAINT table_ebscb_spa_log04_pkey PRIMARY KEY (stmtserial) ) WITH ( OIDS=FALSE ); ALTER TABLE table_ebscb_spa_log04 OWNER TO postgres;* I've already made the first part of the trigger, but Im having trouble doing the second part. The first part of the trigger does this... 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 first part of the trigger will produce every red highlighted square on it) So, what I would like the second part of 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 a 'Lap' is INSERTed in the time_type column, then automatically fill the time_index of the same row, with the same number as the previous(above) time_index cell WHERE time_type = Start and fn_name = to the one in the row where 'Lap' was INSERTed; followed by a dot; and followed by the number that follows the decimal one in the previous time_index cell WHERE time_type and fn_name = to the ones in the row where 'Lap' was INSERTed, that are not before(above) any row WHERE time_type = Start and fn_name = the same to one in the row where 'Lap' was INSERTed. If there isn't anyone, then start counting from 1 (0.1). (I know it seems kinda odd in words, but pls look at the example table image, this second part of the trigger will produce every green highlighted square on it) So, this is what I have made so far... * CREATE OR REPLACE FUNCTION timelog() RETURNS trigger AS $BODY$ DECLARE t_ix real; n int; BEGIN IF NEW.time_type = 'Start' THEN SELECT t.time_index FROM table_ebscb_spa_log04 t WHERE t.fn_name = NEW.fn_name AND t.time_type = 'Start' ORDER BY t.stmtserial 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; ELSE IF NEW.time_type = 'Lap' THEN SELECT t.time_index FROM table_ebscb_spa_log04 t WHERE t.fn_name = NEW.fn_name AND t.time_type IN ('Start', 'Lap') ORDER BY t.stmtserial DESC LIMIT 1 INTO t_ix; GET DIAGNOSTICS n = ROW_COUNT; IF (n = 0) THEN t_ix := 1; ELSE t_ix := t_ix + 0.1; END IF; END IF; END IF; NEW.time_index = t_ix; return NEW; END $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION timelog() OWNER TO postgres;* Im stuck after the ELSE IF, I don't know how to evaluate two conditions in the same expression [t.time_type = 'Start' OR 'Lap'] I don't know what the syntax should be, if I got to use "IN" or "=" or what else. Hope some good PostgreSQL fellow programmer could give me a hand. I have read many of postgres documentation chapters, with no clue.
Might want to reread this section:): http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-CONDITIONALS 40.6.2.2. IF-THEN-ELSE Examples: IF parentid IS NULL OR parentid = ''
Thanks Advanced. [1]: http://i.stack.imgur.com/WIhEO.jpg -- View this message in context: http://postgresql.nabble.com/INSERT-a-real-number-in-a-column-based-on-other-columns-OLD-INSERTs-tp5854916.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
-- Adrian Klaver adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
