PL/Python SQL error code pass-through

Hi all,

Here's a little SQL snippet that exposes an apparent regression in the 9.1.x PL/Python behavior:

---clip---
# cat foo.sql
\set VERBOSITY 'verbose'

CREATE table bar (a INTEGER CONSTRAINT hello CHECK (a > 1));

CREATE OR REPLACE FUNCTION foo ()
RETURNS integer
AS $$
plpy.execute("INSERT INTO bar (a) VALUES (2)")
plpy.execute("INSERT INTO bar (a) VALUES (1)")
return 123
$$ LANGUAGE plpythonu;

SELECT * FROM foo();
---clip---

PostgreSQL 9.0 behavior:

---clip---
# psql < foo.sql
CREATE TABLE
CREATE FUNCTION
WARNING: 01000: PL/Python: plpy.SPIError: unrecognized error in PLy_spi_execute_query
CONTEXT: PL/Python function "foo"
LOCATION: PLy_elog, plpython.c:3532
ERROR: 23514: new row for relation "bar" violates check constraint "hello"
CONTEXT: SQL statement "INSERT INTO bar (a) VALUES (1)"
PL/Python function "foo"
LOCATION: ExecConstraints, execMain.c:1330
---clip---

Note the proper 23514 error code.

PostgreSQL 9.1.1 behavior:

---clip---
# psql < foo.sql
ERROR: 42P07: relation "bar" already exists
LOCATION: heap_create_with_catalog, heap.c:1011
CREATE FUNCTION
ERROR: XX000: spiexceptions.CheckViolation: new row for relation "bar" violates check constraint "hello"
CONTEXT: Traceback (most recent call last):
PL/Python function "foo", line 3, in <module>
plpy.execute("INSERT INTO bar (a) VALUES (1)")
PL/Python function "foo"
LOCATION: PLy_elog, plpython.c:4502
---clip---

In fact, all SQL error that occur within PL/Python seem to be returned with the "XX000" error code. This is a bit of a problem for client-side logic that detects e.g. constraint violations based on the SQL error code.

A small patch that includes passing thru the SQL error code is attached.

Test run with PostgreSQL 9.1.1 + patch:

---clip---
# psql < foo.sql
ERROR: 42P07: relation "bar" already exists
LOCATION: heap_create_with_catalog, heap.c:1011
CREATE FUNCTION
ERROR: 23514: spiexceptions.CheckViolation: new row for relation "bar" violates check constraint "hello"
CONTEXT: Traceback (most recent call last):
PL/Python function "foo", line 4, in <module>
plpy.execute("INSERT INTO bar (a) VALUES (1)")
PL/Python function "foo"
LOCATION: PLy_elog, plpython.c:4504
---clip---

Cheers!

- Mika