pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHrm...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