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

Question

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.

As an aside, Postgres doesn't have "stored procedures", only "functions". This may seem a pedantic comment, but the crucial difference is that a function can only ever return something, not output it as a result set like a stored procedure would in some other DBMSes. — IMSoP
– IMSoP, Commented Nov 7, 2013 at 0:09
Interestingly, it seems to work as expected under Postgres 9.2, as shown in this SQLFiddle demo, so whatever is going on was presumably considered a bug and fixed at some point. — IMSoP
– IMSoP, Commented Nov 7, 2013 at 0:20

IMSoP · Accepted Answer · 2013-11-07 00:30:33Z

6

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.)

answered Nov 7, 2013 at 0:30

IMSoP

99.7k18 gold badges135 silver badges183 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Homunculus Reticulli Over a year ago

+1 for carefully explaining why the error occurs. I would never have figured this out by relying on the generated error message alone.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related