Re: Using a Storedprocedure as a View

Alexander Hachmann wrote:
> Hello,
> I know that this was discussed many times, but all the answers did not help
> me yet.
> I need to create a Procedure that returns the same data as a view does
> except, that the the
> data was filtered by a parameter.
> I want to execute a procedure with a parameter and get back a 'view'.
> I only made prcedures giving back simple values. Can they give back data as
> Views does.
> I dont want the procedure to create a view on which i have to query again
> because the query the Procedure does
> can go over more stages than just this one. The procedure calls another
> procedure and handles this as a subselect.
> Can any one help me?

Does this example help?

Assuming you have a table called "mytable" that you want to view filtered:

CREATE FUNCTION filtered_results(DATE)
RETURNS SETOF mytable
AS '
SELECT * FROM mytable WHERE important_date > $1;
' LANGUAGE SQL;

Of course, this is pretty simple. If your view is more complicated, you'll
probably have to define a custom type, and your select statement will be
more complex. I get the idea from your post that you're having trouble
getting started, so I figured a simple example might help? Actually,
here's a more complex example:

create table customer (
id serial,
name text );
create table invoice (
id serial,
customer int,
amount decimal,
paid boolean );

create type unpaid_invoices as (
name text,
invoice int,
amount decimal );

create function list_unpaid_invoices()
returns setof unpaid_invoices
as '
select name, invoice.id as invoice, amount
from invoice join customer on invoice.customer=customer.id
where not paid;
' language sql;

Hope these help. If not, you might want to just provide the
view definition so folks can give you more specific help.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com