Обсуждение: PL/PGSql function within a view definition
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);
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);
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.
--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
--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.