3

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?

3

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:

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:

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.