"column ... does not exist" error when calling an SQL procedure (new in PostgreSQL 11)

Question

Using PostgreSQL 11 Beta 2, a very simple trading_holiday table is created in the config schema:

DROP TABLE IF EXISTS config.trading_holiday;
CREATE TABLE config.trading_holiday
(
 id smallint GENERATED ALWAYS AS IDENTITY NOT NULL,
 holiday_date DATE,
 name CHARACTER VARYING(80)
);
ALTER TABLE config.trading_holiday
 ADD CONSTRAINT trading_holiday_pk
 PRIMARY KEY (id);

Then a very simple stored procedure, config.sp_add_holiday is created to add holidays to the table:

CREATE OR REPLACE PROCEDURE config.sp_add_holiday(holiday_date DATE, 
 name CHARACTER VARYING(80))
AS $$
BEGIN
 INSERT INTO config.trading_holiday(holiday_date,name)
 VALUES(sp_add_holiday.holiday_date,sp_add_holiday.name);
END
$$
LANGUAGE PLPGSQL;

Then a simple call is made to add the first holiday:

CALL config.sp_add_holiday(holiday_date='2018-01-01',name='New Years Day');

And I get the following error message:

[2018年08月07日 11:56:18] [42703] ERROR: column "holiday_date" does not exist
[2018年08月07日 11:56:18] Position: 21

Doing a manual insert, e.g.:

INSERT INTO config.trading_holiday(holiday_date,name)
 VALUES('2018-01-01','New Years Day');

Works successfully:

[2018年08月07日 12:04:01] 1 row affected in 2 ms

Despite being new to the PostgeSQL 11 SQL procedure functionality (who isn't?), this seems like such a simple proc. What in the world am I doing wrong?

Answer 1

The problem is with the parameter assignment in the call. To fix:

CALL config.sp_add_holiday(holiday_date => '2018-01-01', name => 'New Years Day');

db<>fiddle here

Explanation

Parameter notation is the same for functions and procedures (new in Postgres 11). The manual:

See Section 4.3 for the full details on function and procedure call syntax, including use of named parameters.

Bold emphasis mine.

You have been using = by mistake. That's the plain old "equal" operator, forming a boolean expression. Obviously not what you intended. The expression is evaluated, Postgres looks for a column named "holiday_date" in the calling SQL context and cannot find it. Hence the error message:

ERROR: column "holiday_date" does not exist

Only => (SQL-standard notation ) or := are valid for parameter assignment in the call. Details in the manual. Related:

• Functions with variable number of input parameters

There is a history of misunderstandings around the use of ...

• ... the operators = and := in plpgsql code.
• ... the use of = as short syntax for DEFAULT in CREATE FUNCTION and now also CREATE PROCEDURE statements.
• ... the assignment notation => and := in function calls.

Further reading:

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

• Erwin Brandstetter: I avoided the term "stored procedure" because it has been abused a lot to talk about functions in the past. See: dba.stackexchange.com/questions/118418/…