Re: Plpgsql function syntax error at first coalesce statement

On 04/25/10 12:32, Tom Lane wrote:
> Jeff Ross<jross@wykids.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@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