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