Thread: Hrm...why is this wrong?

Hrm...why is this wrong?

From

Ken Corey

Date:

04 February 2001, 11:35:04

In trying to use a plpgsql stored proc, I'm getting an error I don't 
understand.

When the select at the bottom of this email is executed, I'm getting the 
message:

ERROR:  parser: parse error at or near "$1"

Any ideas? <Gads I hope it's not something amazingly simple, but I'm sure it 
is...;^>

-- 
Ken Corey, CTO    Atomic Interactive, Ltd.

select 'drop FUNCTION IU_EMPLOYEE(varchar(255), ...);' as Progress;
drop FUNCTION IU_EMPLOYEE( varchar(255), varchar(255), varchar(255), varchar(255), varchar(255), varchar(255),
varchar(255),varchar(255), varchar(255), varchar(255), varchar(255));
 

select 'create FUNCTION IU_EMPLOYEE(varchar(255), ...)' as Progress;
create FUNCTION IU_EMPLOYEE( varchar(255), varchar(255), varchar(255), varchar(255), varchar(255), varchar(255),
varchar(255),varchar(255), varchar(255), varchar(255), varchar(255))
 
RETURNS INT4
AS ' DECLARE   user_name_in alias for $1;   passwd_in alias for $2;   firstname_in alias for $3;   lastname_in alias
for$4;   company_in alias for $5;   addr1_in alias for $6;   addr2_in alias for $7;   city_in alias for $8;   state_in
aliasfor $9;   postcode_in alias for $10;   country_in alias for $11;   userid_calc INT4;   companyid_calc INT4; BEGIN
 
   userid_calc := 0;
   select into companyid_calc COMPANY_ID from COMPANY     where COMPANY_NAME = company_in;   if (companyid_calc is
null)  then     insert into COMPANY (COMPANY_NAME) values (company_in);     companyid_calc :=
currval(''company_company_id_seq'');  end if;
 
   if (companyid_calc is not null)   then     insert into EMPLOYEE ( COMPANY_ID ,              AUTHORIZED , RIGHTS ,
USERNAME, PASSWD , FIRSTNAME ,              LASTNAME , ADDR1 , ADDR2 , CITY , STATE , POSTCODE ,              COUNTRY)
     values (           companyid_calc,0,0,username_in, password_in, firstname_in,           lastname_in,
company_in,addr1_in,addr2_in,city_in,          state_in,postcode_in,country_in        );     userid_calc :=
currval(''employee_employee_id_seq'');  else     rollback;     return 0;   end if;
 
   return userid_calc;
 END;'
LANGUAGE 'plpgsql';

select iu_employee('handtest','password','hand','test','handcompany',
'handaddr','handaddr2','handcity','handstate','handpostcode','handcountry');

Re: Hrm...why is this wrong?

From

Tom Lane

Date:

04 February 2001, 18:13:53

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

Re: Hrm...why is this wrong?

From

Ken Corey

Date:

04 February 2001, 18:22:32

Wow! Answering emails on a Sunday?  Someone should be giving you an award or 
something.

On Sunday 04 February 2001  8:13 pm, you wrote:
> 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?

That's strange...perhaps the difference was a problem with my table 
definition?  *shrug* I also had made a few mistakes, so once I got those 
fixed, the code seems to work again.

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

Hey, that's perfect.  It's okay just so long as the debugging out goes 
*somewhere*...:^)

Thanks, Tom.

-- 
Ken Corey, CTO    Atomic Interactive, Ltd.

Re: Hrm...why is this wrong?

From

"Josh Berkus"

Date:

04 February 2001, 18:35:24

Tom, Ken,

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

I've also found that if, when debugging, you launch
postmaster from a kconsole and leave the process running in
the foreground, you see all sorts of useful debugging info
as execution takes place.  Not quite as complete, but a
*lot* faster than checking the log manually.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco

RE: Hrm...why is this wrong?

From

Michael Davis

Date:

05 February 2001, 14:27:54

When declaring parameters try using varchar rather than varchar(255).  I have used text successfully in the past.

-----Original Message-----
From:    Ken Corey [SMTP:ken@kencorey.com]
Sent:    Sunday, February 04, 2001 6:38 AM
To:    pgsql-sql@postgresql.org
Subject:    Hrm...why is this wrong?

In trying to use a plpgsql stored proc, I'm getting an error I don't 
understand.

When the select at the bottom of this email is executed, I'm getting the 
message:

ERROR:  parser: parse error at or near "$1"

Any ideas? <Gads I hope it's not something amazingly simple, but I'm sure it 
is...;^>

-- 
Ken Corey, CTO    Atomic Interactive, Ltd.

select 'drop FUNCTION IU_EMPLOYEE(varchar(255), ...);' as Progress;
drop FUNCTION IU_EMPLOYEE( varchar(255), varchar(255), varchar(255), varchar(255), varchar(255), varchar(255),
varchar(255),varchar(255), varchar(255), varchar(255), varchar(255));
 

select 'create FUNCTION IU_EMPLOYEE(varchar(255), ...)' as Progress;
create FUNCTION IU_EMPLOYEE( varchar(255), varchar(255), varchar(255), varchar(255), varchar(255), varchar(255),
varchar(255),varchar(255), varchar(255), varchar(255), varchar(255))
 
RETURNS INT4
AS ' DECLARE   user_name_in alias for $1;   passwd_in alias for $2;   firstname_in alias for $3;   lastname_in alias
for$4;   company_in alias for $5;   addr1_in alias for $6;   addr2_in alias for $7;   city_in alias for $8;   state_in
aliasfor $9;   postcode_in alias for $10;   country_in alias for $11;   userid_calc INT4;   companyid_calc INT4; BEGIN
 
   userid_calc := 0;
   select into companyid_calc COMPANY_ID from COMPANY     where COMPANY_NAME = company_in;   if (companyid_calc is
null)  then     insert into COMPANY (COMPANY_NAME) values (company_in);     companyid_calc :=
currval(''company_company_id_seq'');  end if;
 
   if (companyid_calc is not null)   then     insert into EMPLOYEE ( COMPANY_ID ,              AUTHORIZED , RIGHTS ,
USERNAME, PASSWD , FIRSTNAME ,              LASTNAME , ADDR1 , ADDR2 , CITY , STATE , POSTCODE ,              COUNTRY)
     values (           companyid_calc,0,0,username_in, password_in, firstname_in,           lastname_in,
company_in,addr1_in,addr2_in,city_in,          state_in,postcode_in,country_in        );     userid_calc :=
currval(''employee_employee_id_seq'');  else     rollback;     return 0;   end if;
 
   return userid_calc;
 END;'
LANGUAGE 'plpgsql';

select iu_employee('handtest','password','hand','test','handcompany',
'handaddr','handaddr2','handcity','handstate','handpostcode','handcountry');

Re: Hrm...why is this wrong?

From

Jan Wieck

Date:

08 February 2001, 09:04:55

Ken Corey wrote:
> Wow! Answering emails on a Sunday?  Someone should be giving you an award or
> something.
>
> On Sunday 04 February 2001  8:13 pm, you wrote:
> > 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?
>
> That's strange...perhaps the difference was a problem with my table
> definition?  *shrug* I also had made a few mistakes, so once I got those
> fixed, the code seems to work again.
>
> > 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:
>
> Hey, that's perfect.  It's okay just so long as the debugging out goes
> *somewhere*...:^)
   Another lesser known trick is to add
   #option dump
   at  the  top  of  the  function  body (before DECLARE).  This   causes the PL/pgSQL compiler to dump out the entire
function  after  parsing  where  you  can  see all the SPI queries that   later will get executed.
 


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com