On 04/25/10 12:32, Tom Lane wrote:
Jeff Ross<jross@xxxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
