Thread: INSERT a number in a column based on other columns OLD INSERTs
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.
--
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.
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
On Sat, 20 Jun 2015 10:44:21 -0700 (MST)
litu16 <litumelendez@gmail.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.
--
Bill Moran
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@gmail.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.
>
On 06/20/2015 12:41 PM, Charles Clavadetscher wrote: > 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 := Yea, I can't seem to remember this part of the docs: " Equal (=) can be used instead of PL/SQL-compliant :=." > > Bye > Charles > -- Adrian Klaver adrian.klaver@aklaver.com
Yes I have tried with t_ix = 1; or t_ix := 1; I get the same result, it just doesn't do anything, I have also tried with AFTER and BEFORE, when I use BEFORE I get this error ERROR: cannot return non-composite value from function returning composite type CONTEXT: PL/pgPSQL function on_ai_mytable() line 17 at RETURN <<<<<<<<< line 17 is... "NEW.time_index = t_ix;" -- View this message in context: http://postgresql.nabble.com/INSERT-a-number-in-a-column-based-on-other-columns-OLD-INSERTs-tp5854577p5854581.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Hi, thanks
yes I was using AFTER, but it only works with BEFORE
so finally I got it to work.
thanks to all
Im just still wondering
why here...
*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;*
I have to put return NEW, instead of NEW.time_index = t_ix????
Thanks to all again.
--
View this message in context:
http://postgresql.nabble.com/INSERT-a-number-in-a-column-based-on-other-columns-OLD-INSERTs-tp5854577p5854602.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Hi The two things have nothing in common. With NEW.time_index = t_ix you set the field with a value and with return you return the record (with the modified field) to make the insert. You have to return NEW, because you have a trigger function. The function must return a record of the same type as the table the trigger has been created for. You can see it as this: You make an insert into a table that has a trigger implemented in a trigger function. BEFORE it is inserted, the record is passed as NEW to the trigger function. In the function, in this case, you modify a field of NEW and return the modified record. Now the insert is done as usual using the NEW record returned by the trigger function. Bye Charles On 6/21/2015 03:07, litu16 wrote: > Hi, thanks > yes I was using AFTER, but it only works with BEFORE > so finally I got it to work. > thanks to all > > Im just still wondering > why here... > > *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;* > > I have to put return NEW, instead of NEW.time_index = t_ix???? > > Thanks to all again. > > > > -- > View this message in context: http://postgresql.nabble.com/INSERT-a-number-in-a-column-based-on-other-columns-OLD-INSERTs-tp5854577p5854602.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > >
Just a final note. If your trigger needs to handle updates or deletes, then you may need to use OLD or both (OLD and NEW) and return OLD instead of NEW, depending on what you want to achieve. You also may return null to avoid the action to be performed, but this only works with "before" triggers. I would recommend you to read the documentation on trigger functions. It is excellent and clarifies quite a lot how things work. Bye Charles On 6/21/2015 16:49, Charles Clavadetscher wrote: > Hi > > The two things have nothing in common. With NEW.time_index = t_ix you > set the field with a value and with return you return the record (with > the modified field) to make the insert. > You have to return NEW, because you have a trigger function. The > function must return a record of the same type as the table the > trigger has been created for. You can see it as this: > > You make an insert into a table that has a trigger implemented in a > trigger function. > BEFORE it is inserted, the record is passed as NEW to the trigger > function. > In the function, in this case, you modify a field of NEW and return > the modified record. > Now the insert is done as usual using the NEW record returned by the > trigger function. > > Bye > Charles > > On 6/21/2015 03:07, litu16 wrote: >> Hi, thanks >> yes I was using AFTER, but it only works with BEFORE >> so finally I got it to work. >> thanks to all >> >> Im just still wondering >> why here... >> >> *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;* >> >> I have to put return NEW, instead of NEW.time_index = t_ix???? >> >> Thanks to all again. >> >> >> >> -- >> View this message in context: >> http://postgresql.nabble.com/INSERT-a-number-in-a-column-based-on-other-columns-OLD-INSERTs-tp5854577p5854602.html >> Sent from the PostgreSQL - general mailing list archive at Nabble.com. >> >> > > >
Thanks again to all Thanks Charles, fortunately I only need to do INSERTS in my database, however I know I have to read postgreSQL documentation, my respect to programmers who have to learn one language after another, after another, an so on. I just know python and other shorter scripting languagues. However I still have more querys to do, in fact only this question is a kinda multiquestions. Do I need to create another thread? or I just reedit this question to add the second subquestion?? Thanks again. Ill be back! -- View this message in context: http://postgresql.nabble.com/INSERT-a-number-in-a-column-based-on-other-columns-OLD-INSERTs-tp5854577p5854636.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 06/21/2015 12:28 PM, litu16 wrote: > Thanks again to all > Thanks Charles, fortunately I only need to do INSERTS in my database, > however I know I have to read postgreSQL documentation, my respect to > programmers who have to learn one language after another, after another, an > so on. I just know python and other shorter scripting languagues. > > However I still have more querys to do, in fact only this question is a > kinda multiquestions. Do I need to create another thread? or I just reedit > this question to add the second subquestion?? Thanks again. Ill be back! I would start a new thread, makes it cleaner now and less confusing in the future when someone finds the thread in the archives. > > > > > > -- -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver wrote: > On 06/20/2015 12:41 PM, Charles Clavadetscher wrote: >> 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 := > > Yea, I can't seem to remember this part of the docs: > > " Equal (=) can be used instead of PL/SQL-compliant :=." This was discussed on -hackers a while ago: http://www.postgresql.org/message-id/flat/52EF20B2E3209443BC37736D00C3C1380876BDC7@EXADV1.host.magwien.gv.at#52EF20B2E3209443BC37736D00C3C1380876BDC7@EXADV1.host.magwien.gv.at It's a rather long and rambling thread, but what I got from it was that "=" for assignments is something that just works by accident, is discouraged and left alive only to avoid breaking code that uses it. Yours, Laurenz Albe