The forgotten assignment operator "=" and the commonplace ":="

The documentation for PL/pgSQL says, that declaration and assignment to variables is done with :=. But a simple, shorter and more modern ^{(see footnote)}= seems to work as expected:

    CREATE OR REPLACE FUNCTION foo() RETURNS int AS $$
    DECLARE
      i int;
    BEGIN
      i = 0;  
      WHILE NOT i = 25 LOOP
          i = i + 1;
          i = i * i;
      END LOOP;
      RETURN i;
    END;
    $$ LANGUAGE plpgsql;

    > SELECT foo();
    25

Please note, that Pl/pgSQL can distinguish assignment and comparison clearly as shown in the line

      WHILE NOT i = 25 LOOP

So, the questions are:

• Didn't I find some section in the docs which mention and/or explains this?
• Are there any known consequences using = instead of :=?

Edit / Footnote:

Please take the "more modern" part with a wink like in A Brief, Incomplete, and Mostly Wrong History of Programming Languages:

1970 - Niklaus Wirth creates Pascal, a procedural language. Critics immediately denounce Pascal because it uses "x := x + y" syntax instead of the more familiar C-like "x = x + y". This criticism happens in spite of the fact that C has not yet been invented.

1972 - Dennis Ritchie invents a powerful gun that shoots both forward and backward simultaneously. Not satisfied with the number of deaths and permanent maimings from that invention he invents C and Unix.

Solution 1:

In PL/PgSQL parser, assignment operator is defined as

assign_operator : '='
                | COLON_EQUALS
                ;

This is a legacy feature, present in source code since 1998, when it was introduced - as we can see in the PostgreSQL Git repo.

Starting from version 9.4 it is oficially documented.

This idiosyncrasy - of having two operators for same thing - was raised on pgsql users list, and some people requested it to be removed, but it's still kept in the core because fair corpus of legacy code relies on it.

See this message from Tom Lane (core Pg developer).

So, to answer your questions straight:

Didn't I find some section in the docs which mention and/or explains this?

You did not find it because it was undocumented, which is fixed as of version 9.4.

Are there any known consequences using = instead of :=.

There are no side consequences of using =, but you should use := for assignment to make your code more readable, and (as a side effect) more compatible with PL/SQL.

Update: there may be a side consequence in rare scenarios (see Erwin's answer)

UPDATE: answer updated thanks to input from Daniel, Sandy & others.

Solution 2:

Q1

This has finally been added to the official documentation with Postgres 9.4:

An assignment of a value to a PL/pgSQL variable is written as:

variable { := | = } expression;

[...] Equal (=) can be used instead of PL/SQL-compliant :=.

Q2

Are there any known consequences using = instead of :=?

Yes, I had a case with severe consequences: Function call with named parameters - which is related but not exactly the same thing.

Strictly speaking, the distinction in this case is made in SQL code. But that's an academic differentiation to the unsuspecting programmer.¹

Consider the function:

CREATE FUNCTION f_oracle(is_true boolean = TRUE) -- correct use of "="
  RETURNS text AS
$func$
SELECT CASE $1
          WHEN TRUE  THEN 'That''s true.'
          WHEN FALSE THEN 'That''s false.'
          ELSE 'How should I know?'
       END
$func$  LANGUAGE sql;

Aside: note the correct use of = in the function definition. That's part of the CREATE FUNCTION syntax - in the style of an SQL assignment.²

Function call with named notation:

SELECT * FROM f_oracle(is_true := TRUE);

Postgres identifies := as parameter assignment and all is well. However:

SELECT * FROM f_oracle(is_true = TRUE);

Since = is the SQL equality operator, Postgres interprets is_true = TRUE as SQL expression in the context of the calling statement and tries to evaluate it before passing the result as unnamed positional parameter. It looks for an identifier is_true in the outer scope. If that can't be found:

ERROR:  column "is_true" does not exist

That's the lucky case and, luckily, also the common one.

When is_true can be found in the outer scope (and data types are compatible), is_true = TRUE is a valid expression with a boolean result that is accepted by the function. No error occurs. Clearly, this is the intention of the programmer using the SQL equality operator = ...

This SQL Fiddle demonstrates the effect.

Very hard to debug if you're unaware of the distinction between = and :=.
Always use the the correct operator.

¹ When using named notation in function calls, only := is the correct assignment operator. This applies to functions of all languages, not just PL/pgSQL, up to and including pg 9.4. See below.

² One can use = (or DEFAULT) to define default values for function parameters. That's not related to the problem at hand in any way. It's just remarkably close to the incorrect use case.

Postgres 9.0 - 9.4: Transition from := to =>

The SQL standard for assignment to named function parameters is => (and Oracle's PL/SQL uses it. Postgres could not do the same, since the operator had previously been unreserved, so it's using PL/pgSQL's assignment operator := instead. With the release of Postgres 9.0 the use of => for other purposes has been deprecated. Per release notes:

Deprecate use of => as an operator name (Robert Haas)

Future versions of PostgreSQL will probably reject this operator name entirely, in order to support the SQL-standard notation for named function parameters. For the moment, it is still allowed, but a warning is emitted when such an operator is defined.

If you should be using => for something else, cease and desist. It will break in the future.

Postgres 9.5: use => now

Starting with this release, the SQL standard operator => is used. := is still supported for backward compatibility. But use the standard operator in new code that doesn't need to run on very old versions.

• Documented in the manual, chapter Using Named Notation.
• Here's the commit with explanation in GIT.

This applies to named parameter assignment in function calls (SQL scope), not to the assignment operator := in plpgsql code, which remains unchanged.

Solution 3:

A partial answer to my own question:

The PL/pgSQL section Obtaining the Result Status shows two examples using a special syntax:

GET DIAGNOSTICS variable = item [ , ... ]; 
GET DIAGNOSTICS integer_var = ROW_COUNT;

I tried both := and = and they work both.

But GET DIAGNOSTICS is special syntax, so one can argue, that this is also not a normal PL/pgSQL assignment operation.

Solution 4:

Reading the Postgresql 9 documentation:

This page lists "=" as an assignment operator in the table on operator precedence.

But strangely this page (assignment operator documentation) doesn't mention it.