Skip site navigation (1) Skip section navigation (2)

Re: Passing arguments to views

From: Greg Stark <gsstark(at)mit(dot)edu>
To: Chris Campbell <chris(at)bignerdranch(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Passing arguments to views
Date: 2006-02-03 04:33:38
Message-ID: 87y80trrh9.fsf@stark.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Chris Campbell <chris(at)bignerdranch(dot)com> writes:

> What do you think? Is this an interesting feature? Is this the right  way to go
> about it, or should I try to get the planner to see through  SQL function
> boundaries

The "right" way to go about this in the original abstract set-theoretic
mindset of SQL is to code the view to retrieve all the rows and then apply
further WHERE clause restrictions to the results of the view. 


So for example this:

>     CREATE VIEW sales_figures($1, $2) AS
>         SELECT ... FROM ... WHERE purchase_date BETWEEN $1 AND $2;

Becomes:

CREATE VIEW sales_figures AS SELECT ... FROM ...

And then you query it with 

SELECT * FROM sales_figures WHERE purchase_date BETWEEN $1 AND $2

sales_figures could have any number of joins and complex where clauses
built-in. It could even be an aggregate grouped by some column (like
purchase_date).

This relies on the SQL optimizer to push the WHERE clause down into the view
to the appropriate depth. Postgres isn't always capable of doing so but it
does a pretty decent job.

-- 
greg


In response to

Responses

pgsql-hackers by date

Next:From: Chris CampbellDate: 2006-02-03 04:55:28
Subject: Re: Passing arguments to views
Previous:From: JohnDate: 2006-02-03 04:04:00
Subject: Where to execute the compiled psql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group