Re: Calling plSQL functions

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,
title text not null,
firstname text not null,
middlename text not null,
lastname text not null,
login text 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 then return the error
if found then
return ''LoginExists'';
end if;

-- now insert the user information into the table
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...'';

return retval;
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/