Обсуждение: 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');
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
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.
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
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');
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