Re: PERSISTANT PREPARE (another point of view) - Mailing list pgsql-sql

Richard Huxton wrote:
>>    Milan Oparnica wrote:>>>>        It's simply to complicated to return recordsets through >>server-side stored
procedures.They are obviously designed to do >>complex data manipulation ...
 
> Richard wrote:>I'm not convinced it's always a win one way or another.>You still haven't said what's "too
complicated"about defining a >function:>>CREATE FUNCTION users_at_dotcom(text) RETURNS SETOF users AS $$>  SELECT *
FROMusers WHERE email LIKE '%@' || $1 || '.com';>$$ LANGUAGE SQL;>  Richard Huxton>  Archonet Ltd
 
-------------------------------------------------------------------
Hi Richard,

It sounds like you suggest not using PREPARED statement nor stored 
procedures to fetch data. What do you think is the best way ?

The example you posted is the only situation where it's simple to use 
stored procedures to fetch data.

--------------------------------------------------------------------
Try to write following simple scenario:

a. Data is retrieved from two tables in INNER JOIN
b. I don't need all fields, but just some of them from both tables

Lets call tables Customers and Orders.

Definition of tables are:
Customers (CustomID INTEGER, Name TEXT(50), Adress TEXT(100))
Orders (OrderID INTEGER, CustomID INTEGER, OrderNum TEXT(10))

Now I need a list of order numbers for some customer:

SELECT C.CustomID, C.Name, O.OrderNum
FROM Customers C INNER JOIN Orders  O ON C.CustomID=O.CustomID
WHERE C.Name LIKE <some input parameter>

Can you write this without defining an SETOF custom data type ?
----------------------------------------------------------------------
NOTE! THIS IS VERY SIMPLIFIED REPRESENTATION OF REAL-LIFE STRATEGY.
----------------------------------------------------------------------
We sometimes have JOINS up to 10 tables.

Besides, using report engines (like Crystal Reports) forces you to avoid 
queries where column order of the recordset can change. If you built a 
report on a query having CutomID,Name,OrderNum columns adding a column 
(CustomID,Name,Adress,OrderNum) will require recompiling the report if 
you want it to give correct results.

Thats one of the reasons we avoid SELECT * statements. Another is 
because some user roles do not have permissions to examine table 
structures. In such cases SELECT * returns error.

I hope I managed to present what I meant by "too complicated" when using 
stored procedures to fetch data.

PREPARED statements do not suffer from such overhead. They simply return 
records as if the statement was prepared in the client.

I will repeat, it took 5 minutes for prepared statement to return 
results of the same SQL that took 16 minutes for the stored procedure to 
do so. SP was written to return SETOF user type. If you want, I'll send 
you the exact SQL and the database. Later we tested other queries and it 
was always better performance using prepared statements then stored 
procedures with SETOF user defined types.

Best regards,

Milan Oparnica