[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] rules question
From:       Richard Huxton <dev () archonet ! com>
Date:       2003-04-30 9:26:51
[Download RAW message or body]

On Wednesday 30 Apr 2003 5:00 am, ed despard wrote:
> hi, i have a table named pursuit which has no data in it, currently to
> get data for the table i have a function that i call like: select *
> from pursuit_select_function(raceid), but i put in the raceid that im
> selecting for.  i would like to be able to treat this table like a view
> and make a query like select * from pursuit where raceid=9 or somthing
> and have a rule rewrite this into the function call. is there a way to
> have a rule that rewrites the query based on the qualifier of the query?

I think you're out of luck here.

For "where raceid=9" it's easy enough - just call "function(9)".
What if someone uses "where raceid in (7,8,9)" - does the planner write that 
as "function(7) or function (8) or function(9)" and how does that interact 
with your side-effects?

> the reason im not just using a normal view is that i have a sequence
> that needs to be reset before each select, so the
> pursuit_select_function resets the sequence and does the select query.

I was thinking you could create a rule with multiple queries for the action, 
but unfortunately that's not supported for ON SELECT rules (according to my 
docs). If it was, you could reset the sequence as the first query, then do 
your processing for the second.

I take it you're using the sequence to number output rows?
-- 
  Richard Huxton


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[prev in list] [next in list] [prev in thread] [next in thread]