On Thu, 27 Jul 2000, Andr Nss wrote:
> I've been looking through the material I have on postgreSQL, but can't seem
> to find an answer to my problem. Very simplied, my tables are something like
> create table news (
> id serial,
> story text,
> publishtime timestamp
> create table news_unpublished (
> I wish to make rule looking something like this:
> create rule newsrule as
> on insert to news do
> if new.publishtime is not null insert into news_unpublished
> I.e. "On an insert to news, if new.publish is not null, insert the new
> post's id into news_unpublished.
> Is this possible?
> Andr Nss
I think a PL/pgSQL trigger will work:
-- Load the PGSQL procedural language
-- This could also be done with the createlang script/program.
-- See man createlang.
CREATE FUNCTION plpgsql_call_handler()
RETURNS OPAQUE AS '/usr/local/pgsql/lib/plpgsql.so'
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
CREATE FUNCTION news_trigproc RETURNS OPAQUE AS '
IF TG_OP = ''INSERT'' THEN
-- unnessary IF above since this is always called on insert only
-- but shows how can detect which OP called the trigger when
-- you make a trigger handle more than just INSERT
IF NEW.publishtime NOTNULL THEN
INSERT INTO news_unpublished VALUES (NEW.id);
' LANGUAGE 'plpgsql';
CREATE TRIGGER newstrigger
AFTER INSERT ON news FOR EACH ROW
EXECUTE PROCEDURE news_trigproc();
In response to
pgsql-sql by date
|Next:||From: Robert B. Easter||Date: 2000-07-27 19:06:45|
|Subject: Re: Conditional rule?|
|Previous:||From: André Næss||Date: 2000-07-27 18:01:00|
|Subject: Re: Conditional rule? |