Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f14KDox16136 for ; Sun, 4 Feb 2001 15:13:50 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.1/8.11.1) with ESMTP id f14KDl906960; Sun, 4 Feb 2001 15:13:47 -0500 (EST) To: Ken Corey cc: pgsql-sql@postgresql.org Subject: Re: Hrm...why is this wrong? In-reply-to: <01020413373508.18716@kenlinux.bithub.org> References: <01020413373508.18716@kenlinux.bithub.org> Comments: In-reply-to Ken Corey message dated "Sun, 04 Feb 2001 13:37:35 +0000" Date: Sun, 04 Feb 2001 15:13:47 -0500 Message-ID: <6957.981317627@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200102/29 X-Sequence-Number: 1197 Ken Corey 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 get ERROR: 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