Re: Hrm...why is this wrong?

Ken Corey <ken@kencorey.com> writes:
> When the select at the bottom of this email is executed, I'm getting the 
> message:
> ERROR:  parser: parse error at or near "$1"

I don't get that; I getERROR:  Attribute 'username_in' not found
which is about what I'd expect for the given function text; maybe you
didn't transcribe it accurately?

Anyway, an invaluable technique for debugging plpgsql functions is to
start psql with debug level 2, so that the queries the plpgsql executor
feeds to the SQL engine get logged in the postmaster log.  (If you don't
run the postmaster with a logfile, you should...)  For example:

$ export PGOPTIONS="-d2"
$ psql regression

regression=# select iu_employee('handtest','password','hand','test','handcompany',
regression(# 'handaddr','handaddr2','handcity','handstate','handpostcode','handcountry');
ERROR:  Attribute 'username_in' not found
regression=#

leaves this in the log file:

DEBUG:  StartTransactionCommand
DEBUG:  query: select iu_employee('handtest','password','hand','test','handcompany',
'handaddr','handaddr2','handcity','handstate','handpostcode','handcountry');
DEBUG:  ProcessQuery
DEBUG:  query: SELECT  0
DEBUG:  query: SELECT  COMPANY_ID from COMPANY where COMPANY_NAME =  $1 
DEBUG:  query: SELECT  ( $1  is null)
DEBUG:  query: SELECT  ( $1  is not null)
DEBUG:  query: insert into EMPLOYEE ( COMPANY_ID , AUTHORIZED , RIGHTS , USERNAME , PASSWD , FIRSTNAME , LASTNAME ,
ADDR1, ADDR2 , CITY , STATE , POSTCODE , COUNTRY) values (  $1 ,0,0,username_in, password_in,  $2 ,  $3 ,  $4 , $5 , $6
,$7 ,  $8 , $9 , $10  )
 
ERROR:  Attribute 'username_in' not found
DEBUG:  Last error occured while executing PL/pgSQL function iu_employee
DEBUG:  line 26 at SQL statement
DEBUG:  AbortCurrentTransaction

There should be a more direct way of doing this, but for now, the
postmaster logfile is the best recourse ...
        regards, tom lane