Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3CFcq816347 for ; Thu, 12 Apr 2001 11:38:53 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 37277; Thu, 12 Apr 2001 08:38:48 -0700 From: "Josh Berkus" Subject: Re: Calling plSQL functions To: Lonnie Cumberland , Josh Berkus Cc: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Thu, 12 Apr 2001 08:38:48 -0700 Message-ID: In-Reply-To: <20010412132354.46676.qmail@web12506.mail.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200104/176 X-Sequence-Number: 2524 Lonnie, > Sorry for the bad terminology as I will try to get it corrected as I > have a > better learning of how to use postgresql properly. No problem. I just wanted to clarify your question; I didn't understand it. > create function register_user(text,text,text,text,text,text,text,text,text) > returns text as ' First off, I believe that you will see some performance improvement if you use VARCHAR instead of TEXT (except, of course, for very long (> 500 chars) strings). Also, not all RDBMS's support the TEXT type, but all do support VARCHAR. FYI. > declare > > client_title ALIAS FOR $1; > first_name ALIAS FOR $2; > middle_name ALIAS FOR $3; > last_name ALIAS FOR $4; > email_address ALIAS FOR $5; > company_name ALIAS FOR $6; > client_login ALIAS FOR $7; > client_passwd ALIAS FOR $8; > client_passwd2 ALIAS FOR $9; > > retval text; > > begin > > -- Look to see if the login is already taken > select * from user_info where login = client_login; > > -- If YES then return the error > if found then > return ''LoginExists''; > end if; This is your problem, right here. The PL/pgSQL handler interprets an un-intercepted SELECT as an attempt to return a rowset from the function. Returning rowsets is entirely the province of stored procedures (not yet supported under postgres) and thus the function handler errors out. What you really want is: > login_check INT4; > begin > > -- Look to see if the login is already taken > SELECT id INTO login_check > FROM user_info where login = client_login; > > -- If YES then return the error > if login_check > 0 then > return ''LoginExists''; > end if; The INTO intecepts the result of the SELECT statement and passes it off to a variable. This would also allow you to return something more informative: > login_check VARCHAR; > begin > > -- Look to see if the login is already taken > SELECT first_name || '' '' || last_name INTO login_check > FROM user_info where login = client_login; > > -- If YES then return the error > if login_check <> '''' then > return ''That login already exists for user '' || login_check || ''. Please choose another.''; > end if; (assuming that first_name and last_name are required and thus NOT NULL). Hope that helps. -Josh Berkus ______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