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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
