Search Postgresql Archives

PL/PGSql function within a view definition

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux