Re: bad variable subst after "AS"

Hello

2010/9/16 Darren Duncan <darren@darrenduncan.net>:
> 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;
>

It's not a bug - just you cannot use a variable there. Table name,
column names are specified in planner time, and cannot be
parametrized.

Regards

Pavel Stehule

p.s. you can use a dynamic SQL - EXECUTE statement - RETURN QUERY
EXECUTE - but it doesn't help you, because you cannot overwrite a
function definition.

> ... 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?
>
> Meanwhile, what is the best way to write f to work around this misbehavior?
>
> Thank you.
>
> -- Darren Duncan
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>