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

Passing arguments to views

From: Chris Campbell <chris(at)bignerdranch(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Passing arguments to views
Date: 2006-02-03 00:12:35
Message-ID: 79BD0E0A-12F6-4D60-A0BD-8FB502112A84@bignerdranch.com (view raw or flat)
Thread:
Lists: pgsql-hackers
I've written some complicated queries that I'd like to save inside  
the server and then call from my clients using a short name. For the  
queries that require no external information, views are perfect. For  
queries that *do* require external information (like a search date  
range), I've used functions (written in the SQL language, because I'm  
just writing plain SQL queries but using $1, $2 tokens for passed-in  
arguments).

When I use these functions, I'm typically joining the results of the  
function with other tables. Since much of the work is being done  
inside the function, the planner can't use both the inside-function  
and outside-function query information when generating a query plan.  
Instead, it has to do Function Scans (planning and executing the SQL  
inside the function at each execution, I'm assuming) and then  
manipulate the output.

Ideally, I'd like to be able to write queries that contain $n tokens  
that will be substituted at execution time, save them on the server,  
and let the query planner plan the whole query before it's executed.

Basically, writing views with arguments.

For example, a "sales_figures" view that requires start_date and  
end_date parameters could be used like this:

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

    SELECT * FROM sales_figures('2005-08-22', '2005-09-14') sf JOIN  
customers c ON (sf.customer_id = c.customer_id)

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 (e.g., enhance the function system instead of  
enhancing the view system)? Would this be a good project for a newbie  
to the code?

I can see that the syntax used above would be problematic: how would  
it distinguish that from a call to a sales_figures() function? Any  
comments about alternative syntax would be welcome, too!

Thanks!

- Chris

Responses

pgsql-hackers by date

Next:From: David FetterDate: 2006-02-03 01:10:06
Subject: Re: Proposal: new pg_dump options --copy-delimiter and
Previous:From: Peter EisentrautDate: 2006-02-02 23:41:16
Subject: Re: Multiple logical databases

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