Skip site navigation (1) Skip section navigation (2)

Help with trigger

From: Michael Satterwhite <michael(at)weblore(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Help with trigger
Date: 2010-12-27 17:57:14
Message-ID: 201012271157.15216.michael@weblore.com (view raw or flat)
Thread:
Lists: pgsql-general
I'm new to PostgreSQL, but have worked with other databases. I'm trying to 
write a trigger to default a timestamp column to a fixed interval before 
another. The test setup is as follows:

create table test
(	date1 timestamp,
	date2 timestamp
);

create or replace function t_listing_startdate() returns trigger as 
$t_listing_startdate$
	begin
		if NEW.date2 is null then
			NEW.date2 := NEW.date1 - interval '7 day';
		end if;
		return NEW;
	end;
$t_listing_startdate$ LANGUAGE plpgsql;

CREATE TRIGGER t_listing_startdate before insert or update on test
	for each row execute procedure t_listing_startdate();

Insert into test(date1) values('May 4, 2012');
INSERT 0 1
test=# select * from test;
        date1        | date2 
---------------------+-------
 2012-04-27 00:00:00 | 
(1 row)

I'm obviously missing something ... and probably something obvious. Why is 
date2 still null?

Thanks much
---Michael

Responses

pgsql-general by date

Next:From: Rob SargentDate: 2010-12-27 17:59:47
Subject: Re: 2 versions of an entity worth distinct table?
Previous:From: Peter EisentrautDate: 2010-12-27 17:50:45
Subject: Re: C++ keywords in headers (was Re: [GENERAL] #include <funcapi.h>)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group