[SQL] 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?
--
Ken Corey, CTOAtomic 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');
Re: [SQL] Hrm...why is this wrong?
Ken Corey <[EMAIL PROTECTED]> 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
Re: [SQL] Hrm...why is this wrong?
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 <[EMAIL PROTECTED]> 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, CTOAtomic Interactive, Ltd.
Re: [SQL] Hrm...why is this wrong?
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 [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco
[GENERAL] Aggregates and joined tables...
Hi all, I have a problem using aggregate function SUM() ... The platform is PostgreSQL 6.5.2 on i386-unknown-freebsd3.2, compiled by gcc 2.7.2.1 . The story is that I need to join two tables with an aggregate function. Here is a sample model : Table A consist of ( fa1 , fa2 ) Table B consist of ( fb1 , fb2 ) Table C consist of ( fc1 , fc2 ,fc3 ) I want to write a query which looks like this : select fa1 - sum(fc3) from A,B,C where fa1=fb1 and fb2=fc2 group by fa1 ; unfortunately I get this in result: ERROR: Illegal use of aggregates or non-group column in target list Any idea ? How can I have my result with only one SQL statement ? Thanks in advance Omid Omoomi _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
[SQL] Directional join syntax in 7.1?
Tom, What's the syntax for directional joins in 7.1 beta? Thanks! -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco
[SQL] Never mind (Directional Joins)
Tom, Sorry! Never mind, I found it in the Development Docs. Grazie! -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco
[SQL] JOIN chaining not working in 7.1 beta 3
Tom, Stephen, I'm trying to parse a query like the following and keep getting various errors (I'd give you the real query but &%^$# Netscape won't do cut-and-paste): SELECT a.1, b.2, c.14, a.2, c.5 FROM a INNER JOIN b ON a.1=b.3 LEFT OUTER JOIN c on a.1=c.2; And I get: ERROR: JOIN/ON CLAUSE REFERS TO 'c' WHICH IS NOT PART OF JOIN. What's wrong here? -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco
Re: [SQL] JOIN chaining not working in 7.1 beta 3
"Josh Berkus" <[EMAIL PROTECTED]> writes: > And I get: > ERROR: JOIN/ON CLAUSE REFERS TO 'c' WHICH IS NOT PART OF > JOIN. > What's wrong here? I don't believe you transcribed your query accurately. My attempt to replicate the complaint works fine: nic=# create table a (one int, two int); CREATE nic=# create table b (two int, three int); CREATE nic=# create table c (two int); CREATE nic=# select a.one, b.two, c.two nic-# FROM a INNER JOIN b ON a.one = b.three nic-# LEFT OUTER JOIN c on a.one = c.two; one | two | two -+-+- (0 rows) I can get the same error message with: nic=# select a.one, b.two, c.two nic-# FROM a INNER JOIN b ON a.one = c.two nic-# LEFT OUTER JOIN c on a.one = c.two; NOTICE: Adding missing FROM-clause entry for table "c" ERROR: JOIN/ON clause refers to "c", which is not part of JOIN which is correct since the a/b join should have a join condition that refers only to a and b. regards, tom lane
Re: [SQL] Bug with rules in 7.0.3?
> On Sat, 3 Feb 2001, Tom Lane wrote: > > > I get > > > > regression=# SELECT * FROM orders; > > order_id | menu_id | price > > --+-+--- > > 1 | 2 |-1 > > (1 row) > > > > which is the correct result given that rules are executed before the > > original query. (Which is why you need a trigger for this...) > > OK. > > I think that Bruce's book is inaccurate then. In section D.19 (p. 299), > also reproduced on the web at > http://www.postgresql.org/docs/aw_pgsql_book/node490.html, this example is > given: > > All new employees must make 5,000 or less: > > CREATE RULE example_5 AS > ON INSERT TO emp > WHERE new.salary > 5000 DO > UPDATE emp SET salary = 5000 > WHERE emp.oid = new.oid; I checked the current create_rule.sgml file, and this example query is no longer in the file. Not sure why it was removed, but it will not appear in 7.1. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[SQL] Re: [GENERAL] Aggregates and joined tables...
"omid omoomi" <[EMAIL PROTECTED]> writes: > The platform is PostgreSQL 6.5.2 on i386-unknown-freebsd3.2, compiled by gcc > 2.7.2.1 . > I want to write a query which looks like this : > select fa1 - sum(fc3) > from A,B,C > where fa1=fb1 and fb2=fc2 > group by fa1 ; > unfortunately I get this in result: > ERROR: Illegal use of aggregates or non-group column in target list Works fine in current sources. Try upgrading to something newer than 6.5.2 ... regards, tom lane
[SQL] pltcl how to get array value
> HI, All!
>
> How can I try
>
> set abc {5,6,7,8}
> return abc{0} # return 5
>
> pltcl
>
> thanks
