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

Re: Conditional rule?

From: "Robert B(dot) Easter" <reaster(at)comptechnews(dot)com>
To: "Andr Nss" <andre(dot)nass(at)student(dot)uib(dot)no>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Conditional rule?
Date: 2000-07-27 18:55:20
Message-ID: 00072715035004.09700@comptechnews (view raw or flat)
Thread:
Lists: pgsql-sql
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
> this:
> 
> create table news (
>    id serial,
>    story text,
>    publishtime timestamp
> )
> 
> create table news_unpublished (
>   news_id
> )
> 
> 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
> values(new.id);
> 
> 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?
> 
> Thanks
> 
> 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'
	LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
	HANDLER plpgsql_call_handler
	LANCOMPILER 'PL/pgSQL';

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);
		END IF;
		RETURN NEW;
	END IF;
' LANGUAGE 'plpgsql';

CREATE TRIGGER newstrigger
	AFTER INSERT ON news FOR EACH ROW
	EXECUTE PROCEDURE news_trigproc();

-- 
			- Robert

In response to

Responses

pgsql-sql by date

Next:From: Robert B. EasterDate: 2000-07-27 19:06:45
Subject: Re: Conditional rule?
Previous:From: André NæssDate: 2000-07-27 18:01:00
Subject: Re: Conditional rule?

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