CODEWITHSUNDEEP
postgresqlstored-procedures
Homunculus Reticulli
Homunculus Reticulli

Reputation: 68476

ERROR: column "xxx" does not exist: Postgresql plpgsql stored procedure

I have written a simple PG SP as a proof of concept. However, I am getting an error mesage when I run the SP. Here is the SQL for the schema and the SP:

CREATE TABLE foobar
(
    forename    VARCHAR(255),
    surname     VARCHAR(32),
    enrolement_ts   TIMESTAMP,
    age     SMALLINT,
    major       VARCHAR(32),
    user_id     INTEGER
);


CREATE OR REPLACE FUNCTION insert_foobar (
       forename         VARCHAR(255),
       surname          VARCHAR(32),
       age      SMALLINT,
       major        VARCHAR(32) ) RETURNS VOID AS $$
    INSERT INTO foobar VALUES (forename, surname, getdate(), age, major, user_id());
$$ LANGUAGE sql;

When I enter the function definition at the command line, I get the following error:

ERROR:  column "forename" does not exist
LINE 6:     INSERT INTO foobar VALUES (forename, surname,...
                                           ^

Can anyone spot/explain what is causing this error?. Clearly, the column forename exists in the table schema, so I don't understand the error message.

Upvotes: 3

Views: 3975

Answers (1)

IMSoP
IMSoP

Reputation: 97968

Prior to PostgreSQL 9.2, arguments to functions written in pure SQL (rather than a procedural language such as pl/pgSQL) could not refer to named parameters by their names, even though the function signature could include them ("for documentation purposes").

The current manual page on SQL functions includes this note:

Note: The ability to use names to reference SQL function arguments was added in PostgreSQL 9.2. Functions to be used in older servers must use the $n notation.

So to work with versions prior to 9.2, your function will need to look like this instead:

CREATE OR REPLACE FUNCTION insert_foobar (
       forename         VARCHAR(255),
       surname          VARCHAR(32),
       age      SMALLINT,
       major        VARCHAR(32) ) RETURNS VOID AS $$
    INSERT INTO foobar VALUES ($1, $2, getdate(), $3, $4, user_id());
$$ LANGUAGE sql;

(I'm presuming getdate() and user_id() are locally defined functions wrapped around now() and whatever logic is needed for the user ID.)

Upvotes: 6

Related Questions