Re: Calling plSQL functions - Mailing list pgsql-sql

Hello Josh,

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.

I will simply show you what I have done which should clarify things better.

I have created a table "user_info" in a database "trdata" with a file called
table.sql:

create sequence user_info_id start 1 minvalue 1;
create table user_info (       id int4 not null default nextval('user_info_id'),       userid text not null,
titletext not null,       firstname text not null,       middlename text not null,       lastname text not null,
logintext not null,       password text not null,       logpass text not null,       email text not null,       company
text,      privatekey text,       primary key (id)
 
);  

----------------------------------------------------------------------

I have then created a PL/pgSQL function called "register_user()" in a file
called register.sql

create function register_user(text,text,text,text,text,text,text,text,text)
returns text as '
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
thenreturn the error   if found then       return ''LoginExists'';   end if;   -- now insert the user information into
thetable   insert into user_info        (title,firstname,middlename,lastname,
email,company,login,password,userid)       values        (client_title,first_name,middle_name,
last_name,email_address,company_name,        client_login,client_passwd,''000000000'');   retval := ''GOOD...'';
returnretval;
 
end;
' language 'plpgsql';  

-------------------------------------------------------------------------------

I then use as simple script "./runtest" to load up (register) the table and
function so that postgresql can see it:

#!/bin/sh
DB=trdata
export DB
FRONTEND="psql -n -e -q"
export FRONTEND
echo "*** destroy old $DB database ***"
dropdb $DB
echo
echo "*** create new $DB database ***"
createdb $DB
echo
echo "*** install PL/pgSQL ***"
$FRONTEND -f mklang.sql -d $DB
echo
echo "*** create $DB tables ***"
$FRONTEND -f tables.sql -d $DB

echo "*** Load Registration Function ***"
$FRONTEND -f register.sql -d $DB 

--------------------------------------------------------------------------

finally I enter the command interpreter by doing "psql trdata" at the prompt.

Once the command interpreter is up and running I tried to access the
"register_user(...) function by entering:

----------------------------------------------------------------------------

trdata=# select 
register_user('title','firstname','middlename','lastname','email','company','login','pwd','pwd');

ERROR:  unexpected SELECT query in exec_stmt_execsql()
trdata=#   

----------------------------------------------------------------------------

My problem is that if I do something like:

trdata=#
trdata=# select abs(-123.45); abs
--------123.45
(1 row)
trdata=#

then things work just fine with the built in PostgreSQL functions.

How can I access my PL/pgSQL functions like the builtin ones?

Thanks for the help and best regards,
Lonnie                                   


__________________________________________________
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail. 
http://personal.mail.yahoo.com/