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
