From: | "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com> |
---|---|
To: | "Jay O'Connor" <joconnor(at)cybermesa(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Inserting a needed record before a select |
Date: | 2003-04-14 17:57:35 |
Message-ID: | Pine.LNX.4.33.0304141153100.18191-100000@css120.ihs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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).
From | Date | Subject | |
---|---|---|---|
Next Message | Jay O'Connor | 2003-04-14 18:04:21 | Re: Inserting a needed record before a select |
Previous Message | Jay O'Connor | 2003-04-14 17:36:39 | Inserting a needed record before a select |