Re: [SQL] Conditional rule?

[Robert B. Easter]

On Thu, 27 Jul 2000, Robert B. Easter wrote:
> On Thu, 27 Jul 2000, André Nęss 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é Nęss
> 

(forgot the BEGIN/END in the function!)

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 '
BEGIN
        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;
END;
' LANGUAGE 'plpgsql';

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

-- 
                        - Robert