Re: views with parameters

Sean Davis <sdavis2@mail.nih.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