PL/pgSQL

PL/pgSQL
Daniel Lundin · BSD · source
Name	PL/pgSQL
Paradigm	Procedural, imperative
Designer	Bruce Momjian, Michael Stonebraker, Tom Lane
Developer	PostgreSQL Global Development Group
Typing	Static, strong
Influenced by	Ada (programming language), Pascal (programming language), Oracle Database
First appeared	1998
License	PostgreSQL License

PL/pgSQL PL/pgSQL is a procedural language for the PostgreSQL relational database system, designed to extend SQL with control structures, local variables, and complex computations. It complements SQL within PostgreSQL Global Development Group distributions and interacts with features developed by contributors such as Bruce Momjian, Tom Lane, and Bruce Momjian's collaborators. The language is used in environments ranging from Amazon Web Services deployments to on-premises systems managed by organizations like IBM, Microsoft, and Red Hat.

Overview

PL/pgSQL originated to provide server-side programming capabilities in PostgreSQL and evolved alongside projects like Postgres95 and standards efforts such as SQL:1999. Contributors from institutions including University of California, Berkeley, University of Wisconsin–Madison, and companies like EnterpriseDB shaped its design. It is commonly used in applications running on Linux, FreeBSD, and Windows Server platforms alongside clients like psql, pgAdmin, and DBeaver. Large deployments by entities such as Netflix, Comcast, Salesforce, Apple Inc., and Twitter have informed operational best practices.

Language Features

PL/pgSQL provides variables, conditional statements, exception handling, and composite types for use with PostgreSQL features like stored procedures, user-defined functions, and triggers. It integrates with indexing systems such as B-tree, GiST, GIN, and BRIN and works with storage engines influenced by designs from Ingres and Oracle Database. The language supports strict typing with compatibility for types defined by extensions like PostGIS, pgcrypto, and hstore maintained by communities including OpenStreetMap contributors and organizations like OSGeo. Security and permission models interact with roles and Access Control List concepts used in enterprises such as Goldman Sachs, Deutsche Bank, and JP Morgan Chase.

Syntax and Examples

Typical PL/pgSQL functions are defined with CREATE FUNCTION syntax and may RETURN scalar types or composite types aligned with tables from International Organization for Standardization-influenced schemas. Examples often show interaction with JSON types influenced by JavaScript ecosystems powering services at Google, Facebook, Instagram, and LinkedIn. Code snippets demonstrate use of DECLARE blocks, FOR loops, IF statements, and RAISE NOTICE for logging, resembling constructs from Ada (programming language), Pascal (programming language), and C (programming language). Developers using tools like JetBrains, Visual Studio Code, and Eclipse integrate PL/pgSQL editing with version control systems such as Git, Subversion, and Mercurial in workflows used by Canonical and Mozilla.

Performance and Optimization

Performance tuning for PL/pgSQL functions involves considerations of query planning by the PostgreSQL query planner and execution performance on hardware platforms from Intel and AMD as deployed by cloud providers Google Cloud Platform, Microsoft Azure, and Amazon Web Services. Optimization techniques include minimizing context switches between SQL and PL/pgSQL, using set-based operations preferred in SQL Server comparisons, and leveraging indexing strategies like GiST and GIN used in large-scale systems at LinkedIn and Pinterest. Profiling uses tools such as pg_stat_statements, EXPLAIN, and third-party analyzers produced by companies like New Relic and Datadog; high-performance use cases intersect with research from MIT, Stanford University, and Carnegie Mellon University.

Security and Permissions

Security for PL/pgSQL functions relies on role-based access controls managed in PostgreSQL clusters administered by teams at NASA, European Space Agency, and financial institutions following regulations such as Sarbanes–Oxley Act and directives from European Union authorities. Privilege escalation risks are mitigated with SECURITY DEFINER and SECURITY INVOKER semantics, auditing via extensions like pgAudit, and integration with authentication mechanisms including Kerberos, LDAP, and OAuth 2.0 used by Google and Facebook. Compliance considerations reference standards from ISO, NIST, and industry-specific requirements adopted by FDA-regulated firms and HIPAA-covered entities.

Extensions and Integration

PL/pgSQL interoperates with a rich ecosystem of PostgreSQL extensions and foreign data wrappers such as PostGIS, pgRouting, pg_trgm, pgcrypto, FDW, and oracle_fdw. Integration patterns include connectors developed by JDBC communities, ODBC drivers used by SAP, and ORMs like Hibernate, ActiveRecord, and SQLAlchemy. Deployment and orchestration often involve Docker, Kubernetes, and configuration management by Ansible, Puppet, and Chef in environments managed by Red Hat and Canonical; monitoring stacks include Prometheus, Grafana, and ELK Stack used by GitHub and GitLab.

Category:Procedural programming languages