From: Paul Lambert <paul.lambert@autoledgers.com.au>
To: pgsql-sql@postgresql.org
Subject: Passing input to a view?
Date: Thu, 10 May 2007 14:03:53 +0800
Message-ID: <4642B5C9.5000505@autoledgers.com.au> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: paul.lambert@autoledgers.com.au
Subject: Re: Passing input to a view?
In-Reply-To: <4642B5C9.5000505@autoledgers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox