Thread: Using a Storedprocedure as a View
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? 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 > 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? > Thx, > Alexander > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend >
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.
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
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 >> 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? >> Thx, >> Alexander >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 8: explain analyze is your friend >>