Re: views with parameters

Sean Davis <sdavis2(at)mail(dot)nih(dot)gov> writes:
> On Nov 30, 2004, at 4:31 AM, Thomas Hermann(Software) wrote:
>> Bruno, thank you for your patience, but I still don't have a clou.
>> Let me illustrate this with a sample from MS SQL-Server:
>>
>> CREATE PROCEDURE au_info_selpub
>> @pubname varchar(40)
>> AS
>> SELECT au_lname, au_fname, title, pub_name
>> FROM authors a INNER JOIN titleauthor ta
>> ON a.au_id = ta.au_id INNER JOIN titles t
>> ON t.title_id = ta.title_id INNER JOIN publishers p
>> ON t.pub_id = p.pub_id
>> WHERE p.pub_name = @pubname
>>
>> EXEC au_info_selpub 'Algodata Infosystems'

> Why not:

> create view au_info_selpub AS SELECT au_lname, au_fname,title,pub_name
> FROM authors a INNER JOIN titleauthor ta
> ON a.au_id = ta.au_id INNER JOIN titles t
> ON t.title_id = ta.title_id INNER JOIN publishers p
> ON t.pub_id = p.pub_id;

> Then:

> select * from au_info_selfpub where pub_name='Algodata Infosystems';

That would be my recommendation too -- a view is way more flexible than
a function (for instance, you could use other WHERE tests with it).

However, if you really really want a function, it would look something
like

create type au_info_selpub_type (au_lname varchar, au_fname varchar,
title varchar, pub_name varchar);
-- I'm guessing about the column data types here, obviously

create function au_info_selpub(varchar) returns setof au_info_selpub_type
as 'SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE p.pub_name = $1'
language sql;

select * from au_info_selpub('Algodata Infosystems');

regards, tom lane