"column ... does not exist" error when calling an SQL procedure (new in PostgreSQL 11)
3
votes
1
answer
19928
views
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] 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?
Asked by Michael Goldshteyn
(241 rep)
Aug 7, 2018, 05:05 PM
Last activity: Aug 8, 2018, 02:39 AM
Last activity: Aug 8, 2018, 02:39 AM