Re: PERSISTANT PREPARE (another point of view)

On 20/07/08 22:16, Milan Oparnica wrote:
> 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>
>
You can do this with cursors, but I'm not sure if you still get the
query caching?

CREATE FUNCTION test(refcursor, input varchar) RETURNS refcursor AS $$
BEGIN
OPEN $1 FOR SELECT C.CustomID, C.Name, O.OrderNum
FROM Customers C INNER JOIN Orders O ON C.CustomID=O.CustomID
WHERE C.Name LIKE '%' || input || '%';
RETURN $1;
END
$$ LANGUAGE plpgsql;

Then to use:

BEGIN;
SELECT test('curs', <some input parameter>);
FETCH ALL FROM curs;
END;

Jeff