Thread: plpgsql - variable's names conflict with table field names

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

Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:
>     When I declare variable with same name as field of table, then I
> have a problem with insert cmd in plpgsql procedure.

Don't do that.

> Is it plpgsql error or my bug?

You could argue it either way, perhaps, but it's unlikely to get
changed.  In general plpgsql cannot tell whether a variable name
appearing in a SQL command ought to be substituted for or not, and so it
just always does it.  I think trying to be smart would create as many
pitfalls as it'd solve.  The best practice is not to use plpgsql
variable names that match table or field names you need to access in the
same procedure.

            regards, tom lane

On Tue, 17 Feb 2004, Tom Lane wrote:

> Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:
> >     When I declare variable with same name as field of table, then I
> > have a problem with insert cmd in plpgsql procedure.
>
> Don't do that.
>
> > Is it plpgsql error or my bug?
>
> You could argue it either way, perhaps, but it's unlikely to get
> changed.  In general plpgsql cannot tell whether a variable name
> appearing in a SQL command ought to be substituted for or not, and so it
> just always does it.  I think trying to be smart would create as many
> pitfalls as it'd solve.  The best practice is not to use plpgsql
> variable names that match table or field names you need to access in the
> same procedure.

I haven't problem with it. I found it when I ported procedures from Solid
db. Can You add big warning about it to plpgsql documentation? This is
very tricky "bug". Insert reports syntax error, ok. But Select reports nothink.

Regard
Pavel

>
>             regards, tom lane
>
> ---------------------------(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
>