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 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. The columns inside those rows can easily be referenced using column names.

Calling the function will return the desired result:

test=# SELECT add_numbers(10); 
 add_numbers 
------------- 
          55 
(1 row) 

If you want to inspect the result set of an SQL statement, PL/Python offers various functions to retrieve more information from the result. Again, those functions are wrappers around what SPI offers on the C level.

The following function inspects a result more closely:

CREATE OR REPLACE FUNCTION result_diag(rows_desired integer)  
  RETURNS integer AS 
$$ 
rv = plpy.execute("SELECT * 
  FROM  generate_series(1, %d) AS id" % (rows_desired)) 
plpy.notice(rv.nrows())  
plpy.notice(rv.status())  
plpy.notice(rv.colnames())  
plpy.notice(rv.coltypes())  
plpy.notice(rv.coltypmods())  
plpy.notice(rv.  str   ()) 
 
return 0 
$$ LANGUAGE 'plpythonu'; 

The nrows() function will display the number of rows. The status() function tells us whether everything worked out fine. The colnames() function returns a list of columns. The coltypes() function returns the object IDs of the data types in the result set. 23 is the internal number of integers as shown in the next listing:

test=# SELECT typname FROM pg_type WHERE oid = 23; 
 typname 
---------  
  int4 
(1 row) 

Then comes typmod. Consider something like varchar(20): the configuration part of the type is what typmod is all about.

Finally, there is a function to return the entire thing as a string for debugging purposes. Calling the function will return the following result:

test=# SELECT result_diag(3);  
NOTICE:  3 
NOTICE:  5 
NOTICE:  ['id']  
NOTICE:  [23]  
NOTICE:  [-1] 
NOTICE:  <PLyResult status=5 nrows=3 rows=[{'id': 1}, 
    {'id': 2}, {'id': 3}]> 
result_diag 
------------- 
        0 
(1 row) 

There are many more functions in the SPI interface that can help you to execute SQL.