"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?

score 3 · Answer 1 · edited Jun 15 '20 at 09:05

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.

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

1 Answers1

Explanation