pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ken Corey <ken@kencorey.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Hrm...why is this wrong?
Date: Sun, 04 Feb 2001 15:13:47 -0500
Message-ID: <6957.981317627@sss.pgh.pa.us> (raw)
In-Reply-To: <01020413373508.18716@kenlinux.bithub.org>
References: <01020413373508.18716@kenlinux.bithub.org>

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 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



view thread (6+ messages)  latest in thread

Message-ID: <6957.981317627@sss.pgh.pa.us>
Permalink:  ../6957.981317627@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/6957.981317627@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, ken@kencorey.com
  Subject: Re: Hrm...why is this wrong?
  In-Reply-To: <6957.981317627@sss.pgh.pa.us>
  References: <01020413373508.18716@kenlinux.bithub.org> <6957.981317627@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox