Re: Calling plSQL functions

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(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco