Re: bad variable subst after "AS"

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?

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

Thank you.

-- Darren Duncan

Hello

2010/9/16 Darren Duncan <darren(at)darrenduncan(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

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

On 09/16/2010 02:33 AM, 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?
>
> Meanwhile, what is the best way to write f to work around this
> misbehavior?
>
> Thank you.
>

Remove the AS clause. You don't need it here at all.

cheers

andrew

Excerpts from Darren Duncan's message of jue sep 16 02:33:37 -0400 2010:

> 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.

Yes.

> 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?

Fixed in 9.0.

> 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.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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 whole point 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