Thank you to the 4 people who replied.
Heikki Linnakangas wrote:
> 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.
>
> If you can't upgrade to 9.0, you'll have to rename the variable or use a different alias in the AS clause.
Good to hear that this is fixed in 9.0; and I do intend to upgrade any week now.
Andrew Dunstan wrote:
> Remove the AS clause. You don't need it here at all.
Okay, that seems to be the best workaround while running under 8.4; or I would
use something like _a1 instead for documentation purposes or for referencing.
Alvaro Herrera wrote:
>> Meanwhile, what is the best way to write f to work around this misbehavior?
>
> Give the column a different alias, one not colliding with a variable name.
In this circumstance, the whole point of the AS clause is, because I was
declaring in the function signature that it exported a table with a column named
a1, I used the AS clause to make sure the selected column was named a1, else
conceptually there would be a type mismatch between declared and actual result. Making them exactly the same is the
wholepoint of the exercise.
I'm in the school of thought that a table column's name is the only proper way
to identify it, rather than the ordinal position being significant for identity,
so even though SQL supports the latter, I consider it a misfeature of SQL that
leads to error-prone code, and try to not rely on it when I can help it.
The fact that Pg would make things work when the result column name is different
than the declared name points squarely to ordinal position as identity, as
that's the only way it could work.
Considering that behavior, I agree that using a different name is reasonable
under 8.4 to make this work.
Pavel Stehule said:
> 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.
>
> 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 think you misunderstood what I was trying to do. In contrast to what you
said, I was *not* expecting the a1 in "AS a1" to be treated as a variable. But
no worries; Heikki/Andrew/Alvaro understood what I meant.
-- Darren Duncan