Database Reference
In-Depth Information
• The plan for the query is not saved, requiring the query text to be parsed
and run through the optimizer at each invocation
We will show a way to properly construct a query string later, but for most uses
simple case parameter passing is enough. So, the
execute(query, [maxrows])
call becomes a set of two statements:
plan = plpy.prepare(<query text>, <list of
argument types>)
res = plpy.execute(plan, <list of values>,
[<row count>])For example, to query if a user
'postgres' is a superuser, use the following:
plan = plpy.prepare("select usesuper from
pg_user where usename = $1", ["text"])
res = plpy.execute(plan, ["postgres"])
print res[0]["usesuper"]
The first statement prepares the query, which parses the query string into a query
tree, optimizes this tree to produce the best query plan available, and returns the
prepared_query
object. The second row uses the prepared plan to query for a
specific user's
superuser
status.
The prepared plan can be used multiple times, so that you could continue to see if
user bob is
superuser
.
res = plpy.execute(plan, ["bob"])
print res[0]["usesuper"]
Caching prepared queries
Preparing the query can be quite an expensive step, especially for more complex
queries where the optimizer has to choose from a rather large set of possible plans;
so, it makes sense to re-use results of this step if possible.
The current implementation of PL/Python does not automatically cache query plans
(prepared queries), but you can do it easily yourself.
