Mastering PostgreSQL 9.6

Using the SPI interface

As with all procedural languages, PL/Python gives you access to the SPI interface. The following example shows how numbers can be added up:

CREATE FUNCTION add_numbers(rows_desired integer)         RETURNS integer AS $$         mysum = 0         cursor = plpy.cursor("SELECT * FROM                     generate_series(1, %d) AS id" % (rows_desired))         while True:                 rows = cursor.fetch(rows_desired)                 if not rows:                         break                 for row in rows:                         mysum += row['id']         return mysum $$ LANGUAGE 'plpythonu'; 

When you try this example out, make sure that the call to cursor is actually a single line. Python is all about indentation, so it does make a difference if your code consists of one or of two lines.

Once the cursor has been created, we can loop over it and add up those ...