Two years ago, a university database assignment led me to do something I hadn't expected: run Python code inside PostgreSQL.
The assignment was straightforward on paper. I needed to accept an XML document, validate it against an XSD schema, and insert the data into relational tables.
I had chosen PostgreSQL for the project. I was already familiar with some of its capabilities, and I wanted to use the assignment to explore the database further.
Things got interesting when I was halfway through the implementation.
PostgreSQL could parse XML, extract values, and turn them into relational data. What I couldn't find was a built-in way to validate a document against an XML Schema Definition (XSD).
Oracle XML DB supported that kind of validation, so switching databases was an option. But I had already invested time in PostgreSQL, and I was curious about whether I could make it work.
That's how I discovered PL/Python.
In this article, I'll walk through the problem, show how I combined PostgreSQL with Python's lxml library, and explain what I learned when I revisited the solution two years later.
This is a practical experiment rather than a recommendation to move XML validation into every database.
The problem: reading XML wasn't enough
Let's start with a simple example.
Suppose an application receives employee records as XML documents. Each document contains information that eventually needs to be stored in a database.
Here's one of our sample records:
<employee>
<firstName>Ada</firstName>
<salary>1200.50</salary>
<hireDate>2024-01-31</hireDate>
</employee>
Nothing particularly complicated here. We have a name, a salary, and a hiring date.
PostgreSQL provides tools for extracting these values, so getting them into relational columns isn't especially difficult.
But receiving a document doesn't mean we should trust its contents.
What if the salary looked like this?
<salary>quite a lot</salary>
As far as XML syntax is concerned, there's nothing wrong with that element. The opening and closing tags match, and the content is valid text.
Our application, however, expects a number.
This is where an XSD comes in.
An XML Schema Definition describes what a document is supposed to look like. It can define required elements, their order, accepted data types, and optional fields.
For our employee document, the rules might say that firstName must be a string, salary must be a decimal, and hireDate must be a date.
Those rules introduce an important distinction:
A document can be well-formed XML without being valid according to an XSD.
That difference was at the heart of my assignment.
Where PostgreSQL stopped short
PostgreSQL has supported XML operations for years.
It includes an xml data type and functions such as XMLPARSE, xpath(), and XMLTABLE. These are useful when you need to inspect XML documents or work with their contents in SQL.
For instance, consider this query:
SELECT xml_is_well_formed_document(
'<employee>
<firstName>Ada</firstName>
<salary>quite a lot</salary>
<hireDate>2024-01-31</hireDate>
</employee>'
);
PostgreSQL returns true.
And that's correct. The document is well-formed XML.
The problem is that a successful syntax check tells us nothing about whether quite a lot is an acceptable salary under our schema.
PostgreSQL 18's documentation makes the limitation clear: while the database can parse and process XML, it doesn't provide built-in validation against XML Schema.
I could extract the values and rely on SQL types and constraints to catch certain problems. But that wouldn't be the same as validating the entire document against the rules defined in an XSD.
I needed to add a missing capability without replacing everything PostgreSQL already handled well.
Enter PL/Python
One feature I particularly like about PostgreSQL is its support for procedural languages.
Besides SQL and PL/pgSQL, PostgreSQL can run functions written in other languages, including Python.
The relevant extension is called plpython3u.
Once the appropriate server-side package is installed, the language can be enabled in a database using:
CREATE EXTENSION plpython3u;
This allows a PostgreSQL function to execute Python code inside the database server's process.
That last part matters.
I wasn't trying to build a separate Python service that would communicate with PostgreSQL. The assignment kept the validation logic inside the database, so I needed Python to run there.
The other half of the solution was lxml, a Python library with support for XML Schema validation.
With those two pieces, I could keep the workflow simple:
Incoming XML + XSD
|
v
PL/Python + lxml
|
XSD validation
/ \
Fail Pass
| |
Reject XMLTABLE
|
v
SQL columns
|
v
INSERT
Python would check the document against the schema. PostgreSQL would handle the relational side.
That division of responsibilities gave me what I needed for the assignment without attempting to reimplement PostgreSQL's existing XML features.
Building the XSD validation step
Before looking at the Python code, let's define the rules for our sample employee.
A minimal XSD could look like this:
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="employee">
<xs:complexType>
<xs:sequence>
<xs:element name="firstName" type="xs:string"/>
<xs:element name="salary" type="xs:decimal"/>
<xs:element name="hireDate" type="xs:date"/>
<xs:element name="nickname"
type="xs:string"
minOccurs="0"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
If you haven't worked much with XSD, focus on two attributes.
The type attribute defines the kind of value each element accepts. And minOccurs="0" makes the nickname element optional.
The xs:sequence declaration also specifies the order in which those elements should appear.
Now we need Python to interpret these rules and check an incoming XML document.
The core validation logic is surprisingly small:
from lxml import etree
# Parse the XSD and build a validator.
xsd_root = etree.fromstring(xsd_bytes, parser())
schema = etree.XMLSchema(xsd_root)
# Parse the incoming XML document.
xml_root = etree.fromstring(xml_bytes, parser())
# Validate the document against the schema.
is_valid = schema.validate(xml_root)
This is a simplified excerpt, not a complete standalone function. The actual implementation includes parser configuration, error handling, and input checks.
The important part is how etree.XMLSchema and schema.validate() work together.
First, lxml parses the schema. Then it compiles the schema into a validator. Finally, the validator checks whether the document follows its rules.
If we provide 1200.50 as the salary, the document passes that type check. If we use quite a lot, it fails because the value doesn't match xs:decimal.
And unlike PostgreSQL's well-formedness check, this validation can also detect missing required elements, unexpected elements, and other violations of the schema.
Returning useful errors
My original university implementation raised a PostgreSQL error whenever XSD validation failed.
That worked for rejecting the document, but it wasn't particularly useful for understanding different failure scenarios.
When I reconstructed the experiment, I changed the helper to return structured JSON results.
Instead of exposing only a boolean, the result identifies whether the failure happened while parsing the XML, preparing the XSD, or validating the document against it.
For example, a schema-validation failure can produce a result with the following fields:
{
"valid": false,
"stage": "xsd_validation",
"category": "xsd_invalid"
}
The helper also includes diagnostic details when available.
This makes it easier for the SQL layer to handle a rejected document consistently.
You can inspect the complete implementation in 03-xsd-validation-cases.sql.
Turning validated XML into SQL rows
Once the XML passes validation, we still need to store its data.
I could have written that part in Python too, but PostgreSQL already has a suitable tool: XMLTABLE.
It lets us read values from an XML document and present them as columns in a SQL query.
Here's a small example:
SELECT *
FROM XMLTABLE(
'/employee'
PASSING XMLPARSE(
DOCUMENT '<employee>
<firstName>Ada</firstName>
<salary>1200.50</salary>
<hireDate>2024-01-31</hireDate>
</employee>'
)
COLUMNS
first_name text PATH 'firstName',
salary numeric PATH 'salary',
hire_date date PATH 'hireDate'
) AS employee;
The query produces a relational row with a name, a numeric salary, and a date.
XMLTABLE does more than select text from the document. It also converts values into the SQL types we specify.
Still, successful SQL type conversion doesn't mean an XML document satisfies its XSD. The schema may impose additional requirements that cannot be inferred from these three columns alone.
That's why I kept schema validation and relational extraction as separate steps.
In the reproducible implementation, a PL/pgSQL function calls the Python validator first:
validation_result :=
xml_research.validate_xml_xsd_case(
xml_text, xsd_text
);
IF COALESCE(
(validation_result->>'valid')::boolean,
false
) IS NOT TRUE THEN
RAISE EXCEPTION USING
ERRCODE = '22023',
MESSAGE = format(
'XML rejected during %s: %s',
validation_result->>'stage',
validation_result->>'category'
);
END IF;
This excerpt runs inside a PL/pgSQL function.
If validation fails, the function raises an exception before reaching the INSERT. I chose SQLSTATE 22023 as the error code for these rejections in the experiment.
If validation succeeds, the function uses XMLTABLE to extract the values and insert them into a temporary relational table.
The result is a small processing pipeline in which Python validates the XML contract and PostgreSQL handles the database operations.
Revisiting the implementation two years later
The original assignment was built on PostgreSQL 15.
For this article, I wanted something more useful than a description of an old university project. So I revisited the implementation and created a reproducible experiment using PostgreSQL 18.6.
The repository is available here:
smaje99/postgresql-xml-plpython
It includes the Docker configuration, SQL scripts, synthetic XML fixtures, and validation tests.
You can run the examples locally without configuring PostgreSQL and PL/Python manually on your machine.
Running the experiment
You'll need Docker Engine with the Compose plugin, plus Python 3 to generate a local password.
Start by cloning the repository:
git clone https://github.com/smaje99/postgresql-xml-plpython.git
cd postgresql-xml-plpython
cp .env.example .env
Generate a password:
python3 -c 'import secrets; print(secrets.token_urlsafe(32))'
Add the generated value to .env as POSTGRES_PASSWORD, then start the database:
docker compose up --build -d
docker compose ps
To run the validation and insertion tests:
docker compose run --rm --no-deps postgres sh -c \
'PGPASSWORD="$POSTGRES_PASSWORD" psql \
-v ON_ERROR_STOP=1 \
-h postgres \
-U "$POSTGRES_USER" \
-d "$POSTGRES_DB" \
-f /workspace/sql/03-xsd-validation-cases.sql'
The script checks its own expected outcomes and raises an error if a test fails.
The repository's README also covers the other XML experiments, including generating XML from relational tables. That was another part of the original assignment, but the focus here is validating incoming documents before storing their data.
When you're done, use docker compose stop to stop the database while keeping its data volume. The README also explains how to remove the disposable environment.
What the tests showed
The updated experiment includes 17 validation cases.
They cover more than just valid and invalid salaries. I also tested missing required elements, invalid dates, malformed XML, malformed XSD, unexpected elements, and namespace mismatches.
Other cases examine how the parser handles DTD declarations, entities, external schema references, oversized inputs, and deeply nested documents.
In the recorded PostgreSQL 18.6 environment, all 17 cases matched their expected results.
The persistence tests inserted two valid employee documents: one with only the required fields and another with an optional nickname.
The temporary table ended up containing:
first_name | salary | hire_date | nickname
-----------+---------+------------+----------
Ada | 1200.50 | 2024-01-31 | NULL
Ada | 1200.50 | 2024-01-31 | Countess
The remaining 15 cases were rejected with SQLSTATE 22023, and the tests verified that they added no rows.
Revisiting the old implementation also exposed a small issue in its error handling.
The historical function called plpy.error() when a document failed validation, followed by a return False. But plpy.error() raises an exception, so execution never reaches that return statement.
In the newer experiment, the Python helper returns structured validation information, and the PL/pgSQL wrapper decides whether to raise a SQL exception.
It's a clearer separation, and it makes the behavior easier to test.
But passing a test suite doesn't automatically make the solution production-ready.
What I would worry about in a real system
The biggest consideration is PL/Python itself.
The u in plpython3u stands for untrusted. PostgreSQL does not sandbox this language as a trusted procedural language, and only superusers can create functions written in it.
Running Python inside the database server is powerful, but it also introduces security and operational responsibilities.
You need to think about who owns the functions, who can execute them, which permissions they have, and what happens when a dependency or runtime needs updating.
XML parsing deserves particular care as well.
In the reproducible experiment, the lxml parser is configured to disable entity resolution, DTD loading, network access, and huge-tree mode. The helper rejects external schema directives and enforces explicit limits on input size and nesting depth.
Those restrictions are part of the laboratory's test policy. They shouldn't be treated as universally safe settings or a substitute for a full security review.
There's another architectural question: who gets to decide which XSD is trusted?
In this experiment, both the XML and the XSD are passed to the function. The parser restrictions address certain technical risks, but they don't establish ownership, approval, or versioning rules for schemas.
A real application would need to deal with that separately.
Performance is also an open question. The helper compiles the schema during validation and performs additional parsing checks. I haven't benchmarked the implementation under load, so I can't claim that it scales efficiently or outperforms application-side validation.
The recorded environment used PostgreSQL 18.6, Python 3.11.2, lxml 4.9.2.0, and libxml2 2.9.14. I haven't tested this implementation across every PostgreSQL release or verified its deployment on managed PostgreSQL services.
These limitations don't invalidate the experiment. They define what it actually demonstrates.
Would I still choose PL/Python today?
I enjoyed working on this solution because it pushed me to explore PostgreSQL beyond the features I already knew.
For the original assignment, it made sense. I needed to keep the validation workflow inside the database, and PL/Python let me add XSD validation while continuing to use PostgreSQL's SQL/XML features.
But if I were designing a production system that receives XML from external users or services today, I wouldn't automatically put the validator inside PostgreSQL.
I'd first look at the application's boundaries.
Where should a document be accepted or rejected? Who owns the schema? How should validation errors reach the client? And what would placing Python inside the database mean for deployment, security, and maintenance?
Validating XML in the application before it reaches the database is another reasonable option. Whether that approach is preferable depends on the system's requirements and constraints.
That's the question I want to explore in the follow-up article: Do we really need PL/Python to process XML in PostgreSQL?
For now, this experiment taught me something valuable.
Knowing that a database can do something is useful. Understanding when it should do it is an entirely different engineering decision.
And sometimes, exploring a limitation is what helps you understand a tool better.
Source code and further reading
The reproducible experiment is published under the GPL-3.0-only license.
- Source code on GitHub
- XSD validation and persistence implementation
- PostgreSQL 18 XML data type documentation
- PostgreSQL XML functions and XMLTABLE
- PostgreSQL PL/Python documentation
- Oracle XML DB schema validation
This is an educational experiment, not a production-ready XML validation service.
Top comments (1)
Thanks for sharing, Sergio. The distinction between well-formed XML and XSD validation is especially clear, and I appreciate your discussion of the experiment’s limitations. Returning structured errors also makes the workflow easier to understand. For the follow-up, I’d be interested in how you would manage trusted, versioned schemas when comparing database-side and application-side validation. That seems central to deciding where this responsibility should live.