Movatterモバイル変換

Callingplpy.execute with a query string and an optional row limit argument causes that query to be run and the result to be returned in a result object.

Iflimit is specified and is greater than zero, thenplpy.execute retrieves at mostlimit rows, much as if the query included aLIMIT clause. Omittinglimit or specifying it as zero results in no row limit.

The result object emulates a list or dictionary object. The result object can be accessed by row number and column name. For example:

rv = plpy.execute("SELECT * FROM my_table", 5)

returns up to 5 rows frommy_table. Ifmy_table has a columnmy_column, it would be accessed as:

foo = rv[i]["my_column"]

The number of rows returned can be obtained using the built-inlen function.

The result object has these additional methods:

The result object can be modified.

Note that callingplpy.execute will cause the entire result set to be read into memory. Only use that function when you are sure that the result set will be relatively small. If you don't want to risk excessive memory usage when fetching large results, useplpy.cursor rather thanplpy.execute.

plpy.prepare prepares the execution plan for a query. It is called with a query string and a list of parameter types, if you have parameter references in the query. For example:

plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", ["text"])

text is the type of the variable you will be passing for$1. The second argument is optional if you don't want to pass any parameters to the query.

After preparing a statement, you use a variant of the functionplpy.execute to run it:

rv = plpy.execute(plan, ["name"], 5)

Pass the plan as the first argument (instead of the query string), and a list of values to substitute into the query as the second argument. The second argument is optional if the query does not expect any parameters. The third argument is the optional row limit as before.

Alternatively, you can call theexecute method on the plan object:

rv = plan.execute(["name"], 5)

Query parameters and result row fields are converted between PostgreSQL and Python data types as described inSection 46.3.

When you prepare a plan using the PL/Python module it is automatically saved. Read the SPI documentation (Chapter 47) for a description of what this means. In order to make effective use of this across function calls one needs to use one of the persistent storage dictionariesSD orGD (seeSection 46.4). For example:

CREATE FUNCTION usesavedplan() RETURNS trigger AS $$    if "plan" in SD:        plan = SD["plan"]    else:        plan = plpy.prepare("SELECT 1")        SD["plan"] = plan    # rest of function$$ LANGUAGE plpythonu;

Theplpy.cursor function accepts the same arguments asplpy.execute (except for the row limit) and returns a cursor object, which allows you to process large result sets in smaller chunks. As withplpy.execute, either a query string or a plan object along with a list of arguments can be used, or thecursor function can be called as a method of the plan object.

The cursor object provides afetch method that accepts an integer parameter and returns a result object. Each time you callfetch, the returned object will contain the next batch of rows, never larger than the parameter value. Once all rows are exhausted,fetch starts returning an empty result object. Cursor objects also provide aniterator interface, yielding one row at a time until all rows are exhausted. Data fetched that way is not returned as result objects, but rather as dictionaries, each dictionary corresponding to a single result row.

An example of two ways of processing data from a large table is:

CREATE FUNCTION count_odd_iterator() RETURNS integer AS $$odd = 0for row in plpy.cursor("select num from largetable"):    if row['num'] % 2:         odd += 1return odd$$ LANGUAGE plpythonu;CREATE FUNCTION count_odd_fetch(batch_size integer) RETURNS integer AS $$odd = 0cursor = plpy.cursor("select num from largetable")while True:    rows = cursor.fetch(batch_size)    if not rows:        break    for row in rows:        if row['num'] % 2:            odd += 1return odd$$ LANGUAGE plpythonu;CREATE FUNCTION count_odd_prepared() RETURNS integer AS $$odd = 0plan = plpy.prepare("select num from largetable where num % $1 <> 0", ["integer"])rows = list(plpy.cursor(plan, [2]))  # or: = list(plan.cursor([2]))return len(rows)$$ LANGUAGE plpythonu;

Cursors are automatically disposed of. But if you want to explicitly release all resources held by a cursor, use theclose method. Once closed, a cursor cannot be fetched from anymore.