I have an application that I'm porting from MSSQL to PostgreSQL. Part of this
application consists of hundreds of stored procedures that I need to convert
to Postgres functions ... or views?
At first I was going to just convert all MSSQL procedures to Postgres functions.
But now that I'm looking at it, a lot of them may be candidates for views. A
lot of them take on the format of:
SELECT a.cola, b.colb, c.colc FROM a JOIN b JOIN c WHERE a.prikey=$1
Make sure that you typecase correctly. It makes a differnce of order of magnitude when you say 'where intpkey=<somevalue>::int' rather than 'where intpkey=<somevalue>'.
It is called typecasting and highly recommened in postgresql for correctly choosing indexes.
I remember another post on some list, which said pl/pgsql seems to be very strongly typed language compared to MSSQL counterpart. So watch for that as well.
(this is slightly oversimplified, but as a generalization of hundreds of functions, it's pretty accurate)
Now, I know this questions is pretty generalized, and I intend to test before
actually commiting to a particular course of action, but I'm very early in the
conversion and I'm curious as to whether people with more experience than I
think that views will provide better performance than functions containing
SQL statements like the above. The client is _very_ interested in
To my understanding, views are expanded at runtime and considered while preparing plan for the complete (and possibly bigger) query(Consider a view joined with something else). That is not as easy/possible if at all, when it is function. For postgresql query planner, the function is a black box(rightly so, I would say).
So using views opens possibility of changing query plans if required. Most of the times that should be faster than using them as functions.
Of course, the standard disclaimer, YMMV. Try yourself.
Correct me if I am wrong.
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings