Mastering PostgreSQL 12 - Third Edition

Using the SPI interface

Like 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 the cursor is actually a single line. Python is all about indentation, so it does make a difference if your code consists of one or two lines.

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