Using a Storedprocedure as a View

Why not just create another view, which is a view of the view? I have
highly nested views on my databases.

Jon

On Tue, 9 Mar 2004, Alexander Hachmann wrote:

Alexander Hachmann wrote:

I need to create a Procedure that returns the same data as a view does
except, that the the

See:
http://techdocs.postgresql.org/guides/SetReturningFunctions

HTH,

Joe

Alexander Hachmann wrote:

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'.

It is possible to create a function that returns multiple rows, but the
interface is different depending on the language used to implement the
function. Read up on "table functions" in the documentation.

Hello,
That would say, that I have to add new objects to the program that I am
writing.
For each View a new ADO table and a new Source.
I develope an application with Delphi 7 and want to use the
ADOStoredProcedure the same
way as I do with querys etc.
I want to put as much logic as possible into the Database.

Is it even possible to work with procedures this way? Can a procedure return
the Values that I need, a kind of Table?
thx,
Alexander

Why not just create another view, which is a view of the view? I have
highly nested views on my databases.

Jon

On Tue, 9 Mar 2004, 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

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