From: Lonnie Cumberland <lonnie_cumberland@yahoo.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Calling plSQL functions
Date: Thu, 12 Apr 2001 06:23:54 -0700 (PDT)
Message-ID: <20010412132354.46676.qmail@web12506.mail.yahoo.com> (raw)
In-Reply-To: <web-37164@davinci.ethosmedia.com>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: lonnie_cumberland@yahoo.com, josh@agliodbs.com
Subject: Re: Calling plSQL functions
In-Reply-To: <20010412132354.46676.qmail@web12506.mail.yahoo.com>
References: <web-37164@davinci.ethosmedia.com> <20010412132354.46676.qmail@web12506.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox