Re: Plpgsql function syntax error at first coalesce statement

Jeff Ross <jross(at)wykids(dot)org> writes:
> I'm trying to write my first plpgsql function and I'm running into a
> problem that may or may not have to do with a coalesce statement.

No, it's not the coalesce ...

> When I try to run this I get the following error:

> jross(at)acer:/var/www/stars/sql $ psql -f view_all_trainers.sql wykids

> psql:view_all_trainers.sql:189: ERROR: syntax error at or near "$10"
> LINE 1: ... $9 , coalesce(pp_email,'No E-Mail Address') as $10 , coal...
> ^

The problem here is that you've got a collision between a plpgsql
parameter name (email) and a name you are trying to use in the SELECT
statement for a different purpose ("as email" is trying to label a
result column of the SELECT). plpgsql isn't bright enough to figure
out that you didn't mean for it to substitute the parameter's value
into the SELECT at that point, so it tries to do so, via the "$10"
you can see there. (This will get improved in PG 9.0, but that
doesn't help you today.)

You need to avoid such naming conflicts. In this particular case
it might be practical to just drop the AS clauses. In general it's
a good plan to use a separate naming convention for parameters and
plpgsql variables, such as prepending "p_" or "v_" to their names.

regards, tom lane