Thread: dynamic rules?

Is it possible to create a select rule that returns results depending on a
where clause in the original select statement?

For Example (oversimplified):

CREATE TABLE one (data int);
CREATE TABLE two (data int);

I make the query:
SELECT data FROM number WHERE type=1;

If the type is 1 (type=1) I want to return 'SELECT data FROM one;'
If the type is 2 (type=2) I want to return 'SELECT data FROM two;'

Thanks,

Chris

On Thu, Jun 03, 2004 at 21:32:47 -0700, Christopher Dedels <cdedels@mygeotivity.com> wrote:
> Is it possible to create a select rule that returns results depending on a
> where clause in the original select statement?
> 
> For Example (oversimplified):
> 
> CREATE TABLE one (data int);
> CREATE TABLE two (data int);
> 
> I make the query:
> SELECT data FROM number WHERE type=1;
> 
> If the type is 1 (type=1) I want to return 'SELECT data FROM one;'
> If the type is 2 (type=2) I want to return 'SELECT data FROM two;'

If you don't need to do updates, you can do this with a view.
It would look something like:

CREATE VIEW number (data, type) AS SELECT one.data, 1 FROM one UNION SELECT two.data, 2 FROM two;

I didn't test the command so there might be a syntax error in it, but
the approach should work.

On Fri, Jun 04, 2004 at 09:16:04 -0700, Christopher Dedels <cdedels@mygeotivity.com> wrote:
> Bruno,
> 
> Thanks for the response.

You should keep the discussion copied to the list as long as it is on topic.
That way other people can help out.

> I was wondering if I was possible to union the tables if necessary.  So, if
> I choose type=1 it selects from only the 'one' table and likewise with 2.
> However, if I query type=1 or type=2 it will union the two and return the
> results from both tables.

That is a planner issue. I suspect that currently postgres won't be able
to see that a union isn't necessary. However unless performance is a real
problem for you, you are better off using simpler code to help maintainance.