Re: Plpgsql function syntax error at first coalesce statement

On 04/25/10 12:32, Tom Lane wrote:
> 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
>

Thanks as always, Tom.

I dropped the AS clauses and it runs and makes a function but now I have
a different error:

wykids=# select * from view_all_trainers();
ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function "view_all_trainers" line 6 at SQL statement

Now I'm *really* confused. I thought the table structure I created at
the beginning of the function was where the results would be returned
to. I tried a variety of queries including select into and create table
but they didn't work either.

Jeff