pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Hrm...why is this wrong?
6+ messages / 5 participants
[nested] [flat]

* Hrm...why is this wrong?
@ 2001-02-04 13:37  Ken Corey <ken@kencorey.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Ken Corey @ 2001-02-04 13:37 UTC (permalink / raw)
  To: pgsql-sql

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 alias for $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');




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Hrm...why is this wrong?
@ 2001-02-04 20:13  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Ken Corey <ken@kencorey.com>
  0 siblings, 2 replies; 6+ messages in thread

From: Tom Lane @ 2001-02-04 20:13 UTC (permalink / raw)
  To: Ken Corey <ken@kencorey.com>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Hrm...why is this wrong?
@ 2001-02-04 20:25  Ken Corey <ken@kencorey.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 6+ messages in thread

From: Ken Corey @ 2001-02-04 20:25 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

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.



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Hrm...why is this wrong?
@ 2001-02-04 20:35  Josh Berkus <josh@agliodbs.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 6+ messages in thread

From: Josh Berkus @ 2001-02-04 20:35 UTC (permalink / raw)
  To: pgsql-sql

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 information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* RE: Hrm...why is this wrong?
@ 2001-02-05 15:24  Michael Davis <mdavis@sevainc.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Michael Davis @ 2001-02-05 15:24 UTC (permalink / raw)
  To: 'Ken Corey' <ken@kencorey.com>; pgsql-sql

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 alias for $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');




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Hrm...why is this wrong?
@ 2001-02-08 09:38  Jan Wieck <janwieck@Yahoo.com>
  parent: Ken Corey <ken@kencorey.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Jan Wieck @ 2001-02-08 09:38 UTC (permalink / raw)
  To: Ken Corey <ken@kencorey.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2001-02-08 09:38 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-04 13:37 Hrm...why is this wrong? Ken Corey <ken@kencorey.com>
2001-02-04 20:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-02-04 20:25   ` Ken Corey <ken@kencorey.com>
2001-02-08 09:38     ` Jan Wieck <janwieck@Yahoo.com>
2001-02-04 20:35   ` Josh Berkus <josh@agliodbs.com>
2001-02-05 15:24 RE: Hrm...why is this wrong? Michael Davis <mdavis@sevainc.com>

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