Received: from web12506.mail.yahoo.com (web12506.mail.yahoo.com [216.136.173.198]) by postgresql.org (8.11.3/8.11.1) with SMTP id f3CDRt881853 for ; Thu, 12 Apr 2001 09:27:55 -0400 (EDT) (envelope-from lonnie_cumberland@yahoo.com) Message-ID: <20010412132354.46676.qmail@web12506.mail.yahoo.com> Received: from [141.217.140.65] by web12506.mail.yahoo.com; Thu, 12 Apr 2001 06:23:54 PDT Date: Thu, 12 Apr 2001 06:23:54 -0700 (PDT) From: Lonnie Cumberland Subject: Re: Calling plSQL functions To: Josh Berkus Cc: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200104/174 X-Sequence-Number: 2522 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/