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

Re: Passing arguments to views

From: Chris Campbell <chris(at)bignerdranch(dot)com>
To: Greg Stark <gsstark(at)mit(dot)edu>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Passing arguments to views
Date: 2006-02-03 04:55:28
Message-ID: 9DDB0A96-0B63-4C92-9048-F0F9B0F26F97@bignerdranch.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Feb 2, 2006, at 23:33, Greg Stark wrote:

> 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

That was a very simplistic example and didn't illustrate my point --  
I apologize. I was trying to think of something succinct and  
illustrative for a quick mailing list post but came up short.

Maybe a better example would be a situation where you want to do  
substitutions in places other than the WHERE clause? There's no way  
to "push" that out to the calling query. But even in this simple  
case, the easier-to-grok syntax of making a view look like a function  
(and codifying the options for restricting the results as arguments  
to the view) is a nice win in terms of readability and maintainability.

I was hoping that people would overlook my bad example because  
they've had the need for a "view with arguments" tool in their own  
work, and the conversation would just be about how it could be  
implemented. :)

I'll try to distill a better example from some of the projects I'm  
working on.

Thanks!

- Chris


In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2006-02-03 05:01:15
Subject: Re: Persistent error
Previous:From: Greg StarkDate: 2006-02-03 04:33:38
Subject: Re: Passing arguments to views

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