Sample Header Ad - 728x90

"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