Re: Calling plSQL functions - Mailing list pgsql-sql

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
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