Re: Inserting a needed record before a select

On Mon, 14 Apr 2003, Jay O'Connor wrote:

> At 11:57 AM 04/14/2003 -0600, scott.marlowe wrote:
>
> >> What I need to do is to find a way to automallically insert a record if it
> >> doesn'
> >> t exist, prior to a SELECT.
>
> >
> >You're over halfway there.  Now you just need to do it in a trigger.
> >Basically, in older versions of pgsql, you can change your
> >function to return an opaque (in 7.3. it's now a trigger type, not sure
> >about 7.2) and just check every time somebody selects from the table for
> >the row and stick it in.  Then make a trigger on it.
> >
> >\h create trigger
> >Command:     CREATE TRIGGER
> >Description: define a new trigger
> >Syntax:
> >CREATE TRIGGER name { BEFORE | AFTER } { event [OR ...] }
> >    ON table FOR EACH { ROW | STATEMENT }
> >    EXECUTE PROCEDURE func ( arguments )
> >
> >So, after you've got a function that returns the right type,
> >
> >create trigger somename before select on tablename for each statement
> >execute insertdefaultrow(rowid).
>
>
> That's what I was thinking but the docs for CREATE TRIGGER say that the
> event type can be either INSERT, UPDATE, or DELETE so I didn't think that a
> trigger for before a select would work

Crap, sorry, you're right.  You'll need to use a rule.  Rules are simpler
than triggers / functions.  They're described in the programmer's guide,
section 13 for 7.3.2.