plpgsql - variable's names conflict with table field names

From: Pavel Stehule <stehule(at)kix(dot)fsv(dot)cvut(dot)cz>
To: pgsql-bugs(at)postgresql(dot)org
Subject: plpgsql - variable's names conflict with table field names
Date: 2004-02-17 10:48:35
Message-ID: Pine.LNX.4.44.0402171132090.3097-100000@kix.fsv.cvut.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hello

When I declare variable with same name as field of table, then I
have a problem with insert cmd in plpgsql procedure. I can't use this name
of columns list in insert cmd; I get syntax error.

When I use equal names in SELECT cmd, I didn't get error msg, but stored
prodedure don't work.

CREATE TABLE fog2(
idx SERIAL PRIMARY KEY,
cas TIMESTAMP
);

-- work fine
CREATE OR REPLACE FUNCTION errdemo() RETURNS TIMESTAMP AS '
DECLARE _cas TIMESTAMP;
BEGIN SELECT INTO _cas cas FROM fog2 LIMIT 1;
RETURN _cas;
END; ' LANGUAGE plpgsql;

-- don't work
CREATE OR REPLACE FUNCTION errdemo() RETURNS TIMESTAMP AS '
DECLARE cas TIMESTAMP;
BEGIN SELECT INTO cas cas FROM fog2 LIMIT 1;
RETURN cas;
END; ' LANGUAGE plpgsql;

-- works fine
CREATE OR REPLACE FUNCTION errdemo() RETURNS TIMESTAMP AS '
DECLARE cas TIMESTAMP;
BEGIN cas := CURRENT_TIMESTAMP;
INSERT INTO fog2 VALUES(DEFAULT, cas);
RETURN cas;
END; ' LANGUAGE plpgsql;

-- don't work - syntax error

CREATE OR REPLACE FUNCTION errdemo() RETURNS TIMESTAMP AS '
DECLARE cas TIMESTAMP;
BEGIN cas := CURRENT_TIMESTAMP;
INSERT INTO fog2 (cas) VALUES(cas);
RETURN cas;
END; ' LANGUAGE plpgsql;

intra=# select errdemo();
ERROR: syntax error at or near "$1" at character 20
CONTEXT: PL/pgSQL function "errdemo" line 3 at SQL statement
intra=#

Is it plpgsql error or my bug?

Regards
Pavel Stehule

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message hessam 2004-02-17 12:03:34 memory does not return back
Previous Message Harry Hochheiser 2004-02-16 15:38:01 Re: Default Timestamp 'Now' bug with 7.4 on Panther.