Thread: INSERT a real number in a column based on other columns OLD INSERTs
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. 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.
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@aklaver.com
Re: INSERT a real number in a column based on other columns OLD INSERTs
From
"David G. Johnston"
Date:
Might want to reread this section:):On 06/23/2015 11:20 PM, litu16 wrote:
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;*
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 = ''
I only looked at the trigger function but the degree of duplication hurt so I decided to suggest an untested alternative to consider.
--Not Tested
SELECT
COALESCE(
--window function gives you the total count while still returning one row.
--put your logic inside the query
(SELECT CASE WHEN count(*) OVER () = 1
THEN t.time_index
ELSE t.time_index + 0.1
END
FROM table_ebscb_spa_log04 t
WHERE t.fn_name = NEW.fn_name AND
(
t.time_type = 'Start' --you always want start time
OR
t.time_type = NEW.time_type --and also (logical or) Lap time if that matches the NEW value
)
ORDER BY t.stmtserial DESC
LIMIT 1
),
--scalar sub-query returns NULL if not matching records found; coalesce then checks the next argument and, in this case, returns the non-null value of 1
1) --default of 1 if no matching records found
INTO t_ix;
NEW.time_index = t_ix;
David J.
Thanks to everybody I already solved, the correct syntax was... SELECT t.time_index FROM table_ebscb_spa_log04 t WHERE t.fn_name = NEW.fn_name AND (t.time_type = 'Start' OR time_type = 'Lap') ORDER BY t.stmtserial DESC LIMIT 1 INTO t_ix; Thanks for all again. -- View this message in context: http://postgresql.nabble.com/INSERT-a-real-number-in-a-column-based-on-other-columns-OLD-INSERTs-tp5854916p5855071.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 25/06/15 16:06, litu16 wrote: > SELECT t.time_index FROM table_ebscb_spa_log04 t WHERE t.fn_name = > NEW.fn_name AND (t.time_type = 'Start' OR time_type = 'Lap') ORDER BY > t.stmtserial DESC LIMIT 1 INTO t_ix; Please format your SQL, as it makes it easier to read! But, you don't have to follow my conventions, though. :-) By formatting it, I noticed an apparent mistake (in this case, functionally the same). You had time_type = 'Lap' rather than t.time_type = 'Lap'. SELECT t.time_index FROM table_ebscb_spa_log04 t WHERE t.fn_name = NEW.fn_name AND ( t.time_type = 'Start' OR t.time_type = 'Lap' ) ORDER BY t.stmtserial DESC LIMIT 1 INTO t_ix;