Re: [SQL] interval +variable
+ '1 days'::interval ""Fatih Cerit"" <[EMAIL PROTECTED]>, haber iletisinde þunlarý yazdý:[EMAIL PROTECTED] > Dear all > > Is there anyone at there who knows howto use dateadd in pgsql. I have a > problem in my function that is like this ; > > Select into futuredate now() + interval '30 days'; <- this is ok > but how can I use a variable intead of '30 days':( > > Thanks > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] best way to auto-update a field when row is updated
I have the following table FIELD_A| FIELD_B | TSTAMP x y 2005-03-10 14:56:47.456431 TSTAMP = not null default now() What's the best way to always auto-update TSTAMP to it's default value whenever the row gets updated? (e.g. update TABLENAME set FIELD_A='zzz' where FIELD_A='x'; should automatically set TSTAMP to now) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] best way to auto-update a field when row is updated
On Thursday 10 March 2005 02:09 pm, Henry Ortega wrote: > I have the following table > > FIELD_A| FIELD_B | TSTAMP > --- >- x y >2005-03-10 14:56:47.456431 > > TSTAMP = not null default now() > > What's the best way to always auto-update TSTAMP to it's > default value whenever the row gets updated? > (e.g. update TABLENAME set FIELD_A='zzz' where FIELD_A='x'; > should automatically set TSTAMP to now) > trigger on update/insert. > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-351-4152 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] best way to auto-update a field when row is updated
On Thu, 2005-03-10 at 14:09, Henry Ortega wrote: > I have the following table > > FIELD_A| FIELD_B | TSTAMP > > x y 2005-03-10 > 14:56:47.456431 > > TSTAMP = not null default now() > > What's the best way to always auto-update TSTAMP to it's > default value whenever the row gets updated? > (e.g. update TABLENAME set FIELD_A='zzz' where FIELD_A='x'; > should automatically set TSTAMP to now) Here's a simple trigger to do that for ya. -- FUNCTION -- CREATE FUNCTION modtime () RETURNS opaque AS ' BEGIN new.lm :=''now''; RETURN new; END; ' LANGUAGE 'plpgsql'; -- TABLE -- CREATE TABLE dtest ( id int primary key, fluff text, lm timestamp without time zone ); --TRIGGER -- CREATE TRIGGER dtest BEFORE UPDATE or INSERT ON dtest FOR EACH ROW EXECUTE PROCEDURE modtime(lm); -- SQL TESTS -- INSERT INTO dtest (id, fluff) VALUES (1,'this is a test'); INSERT INTO dtest (id, fluff) VALUES (2,'this is another test'); SELECT * FROM dtest; 1 | this is a test | 2003-04-02 10:33:12.577089 2 | this is another test | 2003-04-02 10:33:18.591148 UPDATE dtest SET id=3 WHERE id=1; 3 | this is a test | 2003-04-02 10:34:52.219963 [1] UPDATE dtest SET fluff='now is the time' WHERE id=2; SELECT * FROM dtest WHERE id=2; 2 | now is the time | 2003-04-02 10:38:06.259443 [2] UPDATE dtest SET lm='2003-04-02 08:30:00' WHERE id=3; SELECT * FROM dtest WHERE id=3; 3 | this is a test | 2003-04-02 10:36:15.45687 [3] [1] The timestamp has changed for this record when we changed the id field. [2] The timestamp also changes for the fluff field. [3] We tried to set lm, but the trigger on that field in dtest intercepted the change and forced it ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
