pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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/



view thread (10+ messages)  latest in thread

Message-ID: <20010412132354.46676.qmail@web12506.mail.yahoo.com>
Permalink:  ../20010412132354.46676.qmail@web12506.mail.yahoo.com/
Also on:    postgresql.org/message-id/20010412132354.46676.qmail@web12506.mail.yahoo.com

 · 

reply

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