INSERT a real number in a column based on other columns OLD INSERTs

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&gt;

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Jun 24, 2015 at 5:52 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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;*

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 = ''

​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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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;

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general