Обсуждение: 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@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 >
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@commandprompt.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 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