An improved version of PL/PgSQL -- WORK IN PROGRESS
C PLpgSQL Yacc C++ Makefile
Permalink
Failed to load latest commit information.
src
README.md

README.md

PL/PgSQL 2

Motivation

Changes

SELECT .. INTO vs. TOO_MANY_ROWS

In PL/PgSQL, SELECT .. INTO silently throws away rows after the first one. This is problematic since it actively hides bugs and enforcing the "0 or 1 rows" constraint becomes difficult. In PL/PgSQL 2, the runtime exception TOO_MANY_ROWS is raised when a SELECT query with INTO returns more than one row. If you instead want the first row, add LIMIT 1. This change also means that the behavior of the INTO clause is consistent between SELECTs, INSERTs, UPDATEs and DELETEs.

SELECT .. INTO and the number of result columns

In PL/PgSQL, all of these code lines compile successfully:

  -- _f1 and _f2 are "int" variables

  SELECT 1, 2 INTO _f1;
  SELECT 1 INTO _f1, _f2;
  SELECT a b INTO _f1, _f2 FROM foo; -- note the missing comma

  FOR _f1 IN SELECT 1, 2 LOOP /* .. */ END LOOP;
  FOR _f1, _f2 IN SELECT 1 LOOP /* .. */ END LOOP;

In PL/PgSQL 2, all of these are illegal and raise a compile time exception because the number of columns returned by the query doesn't match the number of expressions in the INTO (or FOR .. IN ) target.

EXECUTE and FOUND

It's a common mistake in PL/PgSQL to introduce code which assumes that EXECUTE sets FOUND. For historical reasons it doesn't. In PL/PgSQL 2 this inconsistency has been fixed, and EXECUTE sets FOUND.

SELECT .. INTO

SELECT .. INTO lists get difficult to read and maintain once the number of elements goes above a handful or so. In PL/PgSQL 2 you can write INTO clauses like this:

  SELECT INTO
    _Foo := Junk.Foo,
    _Bar := Junk.Bar,
    _Baz := Junk.Baz,
    _Bat := MoreJunk.Bat
  FROM Junk
  JOIN MoreJunk ..;

The same goes for RETURNING clauses:

UPDATE Junk
    SET Bar = Junk.Bar + 1
RETURNING INTO
    _Foo := Junk.Foo,
    _Bar := Junk.Bar,
    _Baz := Junk.Baz

The old syntax still works, but when appropriate (or at all times, if you prefer), this newer syntax can be used to make INTO lists easier to read and maintain.

The OUT namespace and OUT parameter visibility

(Do we fully appreciate the implications of this?)

Since they practically only ever cause variable conflicts, OUT parameters are not visible in queries unless prefixed with the special OUT namespace or the function name (which still works for backwards compatibility). For readability, the OUT namespace can also be used in variable assignments to make the intent clear:

OUT.Bar := 1;
SELECT count(*)
INTO OUT.Foo
FROM Junk
WHERE Junk.Foo >= OUT.Bar + 20;
RETURN;

In assignment contexts where there's no possible ambiguity (such as lines one and three in the above example), prefixing OUT parameters with "OUT." is not required, but it is still recommended for readability.