Unanswered: Execute function to get the current date
Hi.. first post...
I want to do something very simple... when a record is inserted into a table, or modified, I want to set a date field in the record to the current date. I know I can do that by creating a function that simply returns the current date and then using..
(execute function MyGetCurDate() into datetest.date1);
But as Infx has date functions already I thought... why write my own? Surely from my trigger I can do something like...
(execute function CURRENT() into datetest.date1);
(execute function TODAY() into datetest.date1);
Or (this is the one I think should work)...
(execute function DATE(TODAY) into datetest.date1);
But no luck...
However this does work (or at least the trigger compiles)
(execute function LAST_DAY(TODAY,'Mon') into datetest.date1);
... although it does not do what I want it to do.
This also seems to work (or again it compiles... I have not tested it)
(update datetest set date1 = TODAY where id = new.id);
But that is horrible! ... and I am not sure it will actually work... I would rather write a function.
I see from Time Functions that the likes of DATE is defined as a function.
This might make it clear... this works... but requires that I define a dumb function that calls a function...
drop table datetest;
create table datetest (id serial, date1 date, date2 date, valueX int) ;
drop function getcurdate;
create function getcurdate() returning date
create trigger datetest_change update on datetest
referencing old as old new as new
for each row
when ((old.valueX != new.valueX ) )
(execute function getcurdate() into datetest.date1);
insert into datetest values (0,'1/1/2000','1/1/2000',10);
update datetest set valuex = valuex +1;
select date1 from datetest;