Message-ID: <4642B5C9.5000505@autoledgers.com.au>
Date: Thu, 10 May 2007 14:03:53 +0800
From: Paul Lambert <paul.lambert@autoledgers.com.au>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Passing input to a view?
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Is it possible to define a view to use input parameters rather than a 
hard-wired value in a where clause?

I.e. I have the following view:

CREATE OR REPLACE VIEW invoiced_repairs AS
SELECT ro_header.ro_number, ro_header.received_date, vehicles.vehicle_id,
	vehicles.model, vehicles.engine, vehicles.transmission, 
ro_header.odometer_reading,
	ro_header.service_description,
	(
          CASE WHEN (vehicles.month_of_manufacture <> '' AND 
vehicles.year_of_manufacture <> '')
	 THEN
		(vehicles.month_of_manufacture || '/' || vehicles.year_of_manufacture)
	 ELSE
		''
          END
         ) AS date_of_manufacture,
	vehicles.identification_number, vehicles.engine_number from ro_header
INNER JOIN vehicles USING (vehicle_address)
WHERE ro_header.received_date between '1-jan-2007' AND '10-May-2007';
ALTER TABLE invoiced_repairs OWNER TO postgres;

Instead of having it set to 1-Jan-2007 and 10-May-2007 for the dates, 
can I specify that those values should be passed to the view? Or would I 
need to change this to an SQL language function?

I can't find anything in the documentation that answers this, but maybe 
I'm not looking hard enough.

Thanks in advance.

-- 
Paul Lambert
Database Administrator
AutoLedgers