PL/pgSQL - Programming language

PL/pgSQL is a programming language created in 1998 by Jan Wieck.

#533on PLDB

26Years Old

25kRepos

PL/pgSQL (Procedural Language/PostgreSQL) is a procedural programming language supported by the PostgreSQL ORDBMS. It closely resembles Oracle's PL/SQL language. Implemented by Jan Wieck, PL/pgSQL first appeared with PostgreSQL 6.4, released on October 30, 1998. Read more on Wikipedia...

Tags: programming language
There are at least 24,919 PL/pgSQL repos on GitHub
Early development of PL/pgSQL happened in The PostgreSQL Global Development Group
The Google BigQuery Public Dataset GitHub snapshot shows 8k users using PL/pgSQL in 9k repos on GitHub
Pygments supports syntax highlighting for PL/pgSQL
GitHub supports syntax highlighting for PL/pgSQL
See also: (6 related languages) PL/SQL, Ada, PostgreSQL, SQL/PSM, SQL, Bison
2 PLDB concepts link to PL/pgSQL: Bucardo, PgBouncer

Example from Linguist:

load 'plpgsql';
load 'plpgsql_lint';

DROP FUNCTION IF EXISTS list_sites();
CREATE OR REPLACE FUNCTION list_sites() RETURNS TABLE (fc json) AS
$func$
BEGIN
RETURN QUERY SELECT row_to_json(feat_col) FROM (
    SELECT 'FeatureCollection' AS type, array_to_json(array_agg(feat)) AS features FROM (
            SELECT DISTINCT ON (new_id) 'Feature' AS type, ST_ASGeoJSON(loc.geom)::json AS geometry, row_to_json(
                (SELECT prop FROM (SELECT new_id) AS prop)) AS properties FROM location loc) AS feat) AS feat_col;
END;
$func$ LANGUAGE plpgsql;


DROP FUNCTION IF EXISTS get_observations(character varying, integer);
CREATE OR REPLACE FUNCTION get_observations(kind varchar, site_id integer) RETURNS TABLE (fc json) AS
$func$
BEGIN
    IF kind = 'o2_abs' THEN
        RETURN QUERY SELECT array_to_json(array_agg(row_to_json(obs))) FROM (
            SELECT observation_date AS date, o2_abs AS value FROM oxygen WHERE new_id = site_id) AS obs;
    ELSIF kind = 'o2_rel' THEN
        RETURN QUERY SELECT array_to_json(array_agg(row_to_json(obs))) FROM (
            SELECT observation_date AS date, o2_rel AS value FROM oxygen WHERE new_id = site_id) AS obs;
    ELSIF kind = 'temp' THEN
        RETURN QUERY SELECT array_to_json(array_agg(row_to_json(obs))) FROM (
            SELECT observation_date AS date, temp AS value FROM oxygen WHERE new_id = site_id) AS obs;
    END IF;
END;
$func$ LANGUAGE plpgsql;

Language features

Feature	Supported	Example	Token
Comments	✓
MultiLine Comments	✓
Line Comments	✓
Integers	✓
Floats	✓