Re: bad variable subst after "AS"

On 16/09/10 09:33, Darren Duncan wrote:
> I don't know if this is a bug or not, but if not, it looks like a
> misfeature ...
>
> When executing the following in Pg 8.4.4:
>
> CREATE OR REPLACE FUNCTION f () RETURNS TABLE(a1 INTEGER) AS
> $BODY$
> BEGIN
> RETURN QUERY SELECT a0 AS a1 FROM rv;
> RETURN;
> END;
> $BODY$
> LANGUAGE plpgsql;
>
> ... I get this error:
>
> ERROR: syntax error at or near "$1"
> SQL state: 42601
>
> My impression of this is that Pg is treating the "a1" after the "AS"
> like it was
> a variable reference and so substituted it for $1.
>
> Now that just seems wrong to me. I can understand either "a0" or "rv"
> getting a
> substitution, but something following an "AS" being substituted is just
> wrong.
>
> Is that a bug and if not then what is the rationale for working that
> way, and
> can it be changed?

It's a known misfeature, PL/pgSQL isn't very smart about replacing
variables with parameter markers.

The good news is that this has been completely rewritten in 9.0. The
above will work in 9.0.

> Meanwhile, what is the best way to write f to work around this
misbehavior?

If you can't upgrade to 9.0, you'll have to rename the variable or use a
different alias in the AS clause.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com