Re: parse error in function

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: Uros <uros(at)sir-mag(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: parse error in function
Date: 2004-01-16 22:51:37
Message-ID: 12359.1074293497@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Richard Huxton <dev(at)archonet(dot)com> writes:
> On Thursday 15 January 2004 15:22, Uros wrote:
>> RH> Also - make sure you haven't got a column called 'email' in any query
>> where RH> you are using the variable 'email' - that can cause confusion.
>>
>> Problem was aliases, becasu alias name was the same as column and then the
>> same name was value and rowname. I replace all names like email1 etc. and
>> it works. I hope ;).

> The error messages could be a bit clearer with some of these problems - I've
> had quite a bit of experience now decoding strange parser errors with
> plpgsql.

Would it help any if plpgsql showed the actual string fed to the main
SQL parser? In PG 7.4, the message you would get from Uros' example is

ERROR: syntax error at or near "$1" at character 58
CONTEXT: PL/pgSQL function "fn_insert_entry_pending" line 26 at SQL statement

but I think we could make it produce something like

ERROR: syntax error at or near "$1" at character 58
CONTEXT: PL/pgSQL function "fn_insert_entry_pending" line 26 at SQL statement "INSERT INTO "directory_entry_pending" (id_entry,id_user, $1 , $2 , $3 , $4 , $5 ,hash) VALUES ( $6 , $7 , $8 , $9 , $10 , $11 , $12 ,'1')"

I'm not sure if this would be helpful or just confusing. In particular
notice how the parameter symbols have been renumbered compared to what
was written in the function, which for reference is

INSERT INTO "directory_entry_pending" (id_entry,id_user,caption,url,is_company,is_novelty,expire,hash) VALUES (id_entry_tmp,$1,$2,$5,$6,$7,$8,''1'');

I can see that confusing someone. But at least this would give users a
reasonable shot at understanding what happened. Right now you have to
enable log_statement and dig in the postmaster log to see what's
happening under the hood. (That's how I got the correct string to
exhibit in the example above...)

regards, tom lane

In response to

Browse pgsql-general by date

  From Date Subject
Next Message bill.postgresql-users 2004-01-16 22:52:21 Tool to ease development of plpgsql
Previous Message Nigel J. Andrews 2004-01-16 22:51:05 Re: embedded/"serverless" (Re: serverless postgresql)