PL/PGSql function within a view definition

[Keith Haugh <kmhaugh001@xxxxxxxxx>]

Due to limitations (perceived or real) within my client application I am trying to return a complex dataset which I am assembling using an expensive PL/PGSql function which I would like to wrap in a writeable view.

I have written the function as both a row-level function which returns a ROWTYPE and as a table level function which returns a SETOF ROWTYPES.  In both cases I have encountered issues.  Please keep in mind that my end goal is to create a writeable view that my client app will treat as a simple table.

OPTION ONE - ROWTYPE

--this works correctly.
Select my_func(1);

--and this works correctly
Select my_table.a, my_func(my_table.a)
Where my_table.a in (1,2,3);
--works great.
--however when i create the following view and use the following query...
Create view my_view as select my_table.a as a, my_func(my_table.a)  from my_table;
Select * from my_view where a in (1,2,3);

--the function appears to be run on each row of my_table which is not tolerable due to the size of my_table and the cost of my_func.

Any suggestions on how to force the selection of my_table records prior to executing the function?

OPTION TWO ? SETOF ROWTYPE
--this works correctly.
Select * from my_func2(1);
--however
Select * from my_table, my_func(my_table.a) where my_table.a in (1,2,3);
--appears to be an illegal construct within postgres which prevents me from creating the following view.

Create view my_view as select a, b.* from my_table, my_func(my_table.a) as b;
--to be used in the following manner

Select * from my_view where a in (1,2,3);

Any suggestions on either of these two potential solutions or suggestions as to other methods are greatly appreciated.

---

Never miss a thing.   Make Yahoo your homepage.