Re: Inserting a needed record before a select
От | scott.marlowe |
---|---|
Тема | Re: Inserting a needed record before a select |
Дата | |
Msg-id | Pine.LNX.4.33.0304141153100.18191-100000@css120.ihs.com обсуждение исходный текст |
Ответ на | Inserting a needed record before a select ("Jay O'Connor" <joconnor@cybermesa.com>) |
Ответы |
Re: Inserting a needed record before a select
|
Список | 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).
В списке pgsql-general по дате отправления: