On 8 Sep 2006 14:39:54 -0700, Karen Hill <karen_hill22@xxxxxxxxx> wrote:
"Merlin Moncure" wrote: > you could of course do: > FOR rec IN SELECT * FROM test WHERE $1 is null or mydate > $1 loop [...] > or some such. This was a simple example. In reality, the structure of the query is altered, but there are about 4 different query possibilities in the real problem depending on which values are null or not. My question was is it worth it to use Execute and suffer possible performance issues of having the planner make a new plan every time the Execute command was run?
you can extend the above to an aribtrary number of parameters: select * from t where (in_a is null or in_a > a) and (in_b is null or in_b > b) [...] the server is very smart at optimizing the above. however there are some cases where this is inconvenient, for example a parameter that adds a group by clause. in this case 'execute' is sometimes better. The plan generation is only relevant if the function is quick and generated frequently, or has extremely complex plans (not likely). There is an interesting recent thread in -hackers discussing plan generation you might want to check out. Generally, I don't like dynamic sql because it is more complex to write and can get very tedius, especially with large queries with lots of strings. The major time saver with plpgsql, IMO, is that your queries are first class and dont have to be run through a separate parser.
Don't refcursors consume a lot of database server resources? I wish to avoid that so in practice I use LIMIT and OFFSET to control results.
refcursors are almost always better so long as you remember they can only be held open for the duration of a transaction. OFFSET on the other hand is notoriously inefficient as the offsets grow large. It is fine for small browses however. However, you can just do relative querying to browse large sets which is probably the fastest way of all. merlin
