Thread: Inserting a needed record before a select

Inserting a needed record before a select

From
"Jay O'Connor"
Date:
All,

Hello, I'm new to this list but I'm looking for some information.


What I need to do is to find a way to automallically insert a record if it
doesn'
t exist, prior to a SELECT.

Basically, I'm tracking some information based on date and the client
application when retrieving the information knows will that 'current' is
the record with today's date.

What I'd like is a way of of specifiying that when the SELECT is done,
there will be a check that a record exists for the current date and that if
it doesn't exist, a record will be inserted into the table for the current
date and then the SELECT will return the recors including the newly
inserted one.

What I've tried so far is a function that checks for the record, inserts it
if needed, and then returns a cursor that the client can use, but I was
wondering if there was something more direct.  I thought of a RULE but I
don't see that they can do processing that expressive.

Here's an example: mytable contains the date based stats information with
another id.  All the stats columns are defaulted so just inserting a new
record with the right id and date is sufficient.
=============

CREATE FUNCTION getdatestats (varchar) RETURNS refcursor AS '
DECLARE
    myid ALIAS FOR $1;
    today date;
    ref refcursor;
    dummyrow RECORD;

BEGIN

    ref := 'statscursor';
    SELECT INTO dummyrow * FROM mytable where id = AND statdate = today;
    IF NOT FOUND
        INSERT INTO  mytable (id, statdate) VALUES (myid, today)
    END IF;

    OPEN ref for SELECT * FROM mytable WHERE id=myid;

    RETURN ref;
END;
' LANGUAGE PLPGSQL

=============


Any other ideas?  This works but I was wondering if it was possible to do
it without the client needing to do the transaction/cursor handling
(considering I'm only holding up to 30 days worth of data)

Thanks

Jay O'Connor


Re: Inserting a needed record before a select

From
"scott.marlowe"
Date:
On Mon, 14 Apr 2003, Jay O'Connor wrote:

> All,
>
> Hello, I'm new to this list but I'm looking for some information.
>
>
> What I need to do is to find a way to automallically insert a record if it
> doesn'
> t exist, prior to a SELECT.
>
> Basically, I'm tracking some information based on date and the client
> application when retrieving the information knows will that 'current' is
> the record with today's date.
>
> What I'd like is a way of of specifiying that when the SELECT is done,
> there will be a check that a record exists for the current date and that if
> it doesn't exist, a record will be inserted into the table for the current
> date and then the SELECT will return the recors including the newly
> inserted one.
>
> What I've tried so far is a function that checks for the record, inserts it
> if needed, and then returns a cursor that the client can use, but I was
> wondering if there was something more direct.  I thought of a RULE but I
> don't see that they can do processing that expressive.
>
> Here's an example: mytable contains the date based stats information with
> another id.  All the stats columns are defaulted so just inserting a new
> record with the right id and date is sufficient.
> =============
>
> CREATE FUNCTION getdatestats (varchar) RETURNS refcursor AS '
> DECLARE
>     myid ALIAS FOR $1;
>     today date;
>     ref refcursor;
>     dummyrow RECORD;
>
> BEGIN
>
>     ref := 'statscursor';
>     SELECT INTO dummyrow * FROM mytable where id = AND statdate = today;
>     IF NOT FOUND
>         INSERT INTO  mytable (id, statdate) VALUES (myid, today)
>     END IF;
>
>     OPEN ref for SELECT * FROM mytable WHERE id=myid;
>
>     RETURN ref;
> END;
> ' LANGUAGE PLPGSQL
>
> =============
>
>
> Any other ideas?  This works but I was wondering if it was possible to do
> it without the client needing to do the transaction/cursor handling
> (considering I'm only holding up to 30 days worth of data)

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).


Re: Inserting a needed record before a select

From
"Jay O'Connor"
Date:
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

Thanks


Take care,
Jay


Re: Inserting a needed record before a select

From
"scott.marlowe"
Date:
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.


Re: Inserting a needed record before a select

From
"Jay O'Connor"
Date:
At 12:27 PM 04/14/2003 -0600, scott.marlowe wrote:

>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.

Problem is RULEs don't seem to have the sophistication I need (boolean
logic...search for a record and if it doesn't exists, insert it, then
re-SELECT on the same table)

Take care,
Jay


Re: Inserting a needed record before a select

From
"scott.marlowe"
Date:
On Mon, 14 Apr 2003, Jay O'Connor wrote:

> At 12:27 PM 04/14/2003 -0600, scott.marlowe wrote:
>
> >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.
>
> Problem is RULEs don't seem to have the sophistication I need (boolean
> logic...search for a record and if it doesn't exists, insert it, then
> re-SELECT on the same table)

I think it might be doable anyway.  Since rules allow where clauses, maybe
you could make one that had a where clause to test to see if the row
existed and if not then it calls a function to create the row.

I'm busy wrestling an import script to the ground right now, but I'll try
to see if I can make something like that work when I get a minute.