Mastering Bound Queries in PostgreSQL: A Step-by-Step Guide with Examples

#postgres #sql #mysql #dba

Bound queries, often referred to in the context of prepared statements, are a feature in PostgreSQL (and other relational databases) that allow for query execution with parameter placeholders. These placeholders are then bound to actual values at execution time. This technique can enhance performance, especially for repeated executions of the same query with different values, because the query plan is generated only once at the preparation time and can be reused. It also helps in preventing SQL injection attacks by separating the query structure from the data.

Let's walk through an example to demonstrate how bound queries work in PostgreSQL, including the creation of a table, inserting data, preparing a bound query, executing it, and then deallocating the prepared statement.

1. Table Creation and Data Insertion

First, let's create a simple table named employees and insert some sample data into it.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    department_id INTEGER
);

INSERT INTO employees (name, department_id) VALUES
('Alice', 1),
('Bob', 1),
('Charlie', 2),
('Diana', 2),
('Evan', 3);

2. Preparing a Bound Query

We prepare a query to select employees from a specific department. The $1 symbol is used as a placeholder for the department ID.

PREPARE select_by_department AS
SELECT * FROM employees WHERE department_id = $1;

3. Executing the Prepared Statement

To execute the prepared statement with a specific department ID, use the EXECUTE command.

EXECUTE select_by_department(2);

This query will return employees who belong to the department with department_id = 2.

4. Explaining the Prepared Statement

To understand how PostgreSQL executes this prepared statement, you can use the EXPLAIN command.

EXPLAIN EXECUTE select_by_department(2);

This will show the execution plan without actually running the query.

5. Deallocating the Prepared Statement

After you're done with the prepared statement, it's good practice to deallocate it, especially if it's no longer needed. This frees up resources.

DEALLOCATE select_by_department;

Conclusion

Prepared statements (bound queries) are particularly useful in scenarios where the same query structure is executed repeatedly with different parameters. They can help improve performance by allowing the database to reuse the query plan and also enhance security by preventing SQL injection. This example walked you through the process of creating a table, inserting data, preparing a bound query, executing it with parameters, and cleaning up after.