Thread: Calling plSQL functions
Hello All,
I have created a plSQL function with the "create function ..." which seems to
be ok and is just very simple.
I then wanted to load the function by:
psql -f test.sql -d trdata
which loads my function fine.
the problem is that I do not know how to call this function from the PLSQL
interpreter.
I tried "select reg_user('name','age');"
but it just gives me an error about an unexpected "select ..."
I am writing some plSQL functions and also some "C" interface functions that I
want to load and then call from with the interpreter.
Any ideas?
cheers,
Lonnie
__________________________________________________
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail.
http://personal.mail.yahoo.com/
Lonnie, It may be that you have not linked the PL/pgSQL language in Postgres. It may be that you are putting a SELECT statement inside your function. It is hard to tell, becuase I am unclear on the problem youa re having, exactly. Here's some terminology to help clarify: PL/pgSQL is the extension of SQL written by Jan Wieck used to write functions in PostgreSQL. PSQL is the command-line interface tool for the PostgreSQL database. PL/SQL is a procedure-writing language for Oracle which does not work on PostgreSQL at all, although it was the inspiration for PL/pgSQL. Please re-state you difficulty, and I can probably help you. -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
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/
I solved the problem Josh. It had to do with the "select ..." statement in the function and also I needed to include a "record" variable so the the "select into " could act upon. Thanks for the help anyway. Cheers, Lonnie --- Josh Berkus <josh@agliodbs.com> wrote: > Lonnie, > > It may be that you have not linked the PL/pgSQL language in Postgres. > > It may be that you are putting a SELECT statement inside your function. > > It is hard to tell, becuase I am unclear on the problem youa re having, > exactly. Here's some terminology to help clarify: > > PL/pgSQL is the extension of SQL written by Jan Wieck used to write > functions in PostgreSQL. > > PSQL is the command-line interface tool for the PostgreSQL database. > > PL/SQL is a procedure-writing language for Oracle which does not work on > PostgreSQL at all, although it was the inspiration for PL/pgSQL. > > Please re-state you difficulty, and I can probably help you. > > -Josh Berkus > > > ______AGLIO DATABASE SOLUTIONS___________________________ > Josh Berkus > Complete information technology josh@agliodbs.com > and data management solutions (415) 565-7293 > for law firms, small businesses fax 621-2533 > and non-profit organizations. San Francisco __________________________________________________ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/
Lonnie, > 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. No problem. I just wanted to clarify your question; I didn't understand it. > create function register_user(text,text,text,text,text,text,text,text,text) > returns text as ' First off, I believe that you will see some performance improvement if you use VARCHAR instead of TEXT (except, of course, for very long (> 500 chars) strings). Also, not all RDBMS's support the TEXT type, but all do support VARCHAR. FYI. > 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; This is your problem, right here. The PL/pgSQL handler interprets an un-intercepted SELECT as an attempt to return a rowset from the function. Returning rowsets is entirely the province of stored procedures (not yet supported under postgres) and thus the function handler errors out. What you really want is: > login_check INT4; > begin > > -- Look to see if the login is already taken > SELECT id INTO login_check > FROM user_info where login = client_login; > > -- If YES then return the error > if login_check > 0 then > return ''LoginExists''; > end if; The INTO intecepts the result of the SELECT statement and passes it off to a variable. This would also allow you to return something more informative: > login_check VARCHAR; > begin > > -- Look to see if the login is already taken > SELECT first_name || '' '' || last_name INTO login_check > FROM user_info where login = client_login; > > -- If YES then return the error > if login_check <> '''' then > return ''That login already exists for user '' || login_check || ''. Please choose another.''; > end if; (assuming that first_name and last_name are required and thus NOT NULL). Hope that helps. -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Lonnie Cumberland <lonnie_cumberland@yahoo.com> writes:
> 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)
> [snip]
> begin
> -- Look to see if the login is already taken
> select * from user_info where login = client_login;
> ERROR: unexpected SELECT query in exec_stmt_execsql()
The error message isn't very helpful, but the issue here is that you're
doing a completely useless SELECT --- or at least plpgsql thinks it's
useless, because you're discarding the result. (I think that plpgsql is
being overly anal-retentive about it, since such a query might indeed be
useful if you then examine FOUND or ROW_COUNT, but that's the issue at
the moment.) Try making it a SELECT INTO instead.
regards, tom lane
Thanks for the info Tom, I found that same solution just after I had sent this message to the mailing list. I set up a RECORD variable that should receive the results, but the documentation is unclear as to if the variable will receive ALL of the results from the query of just the first result from the query. Actually I have the need at different places in my functions to sometimes work on the list items returned from a query and also have the need to just work on the first result returned by a query. Can you please tell me how these two can easily be done? Cheers, Lonnie --- Tom Lane <tgl@sss.pgh.pa.us> wrote: > Lonnie Cumberland <lonnie_cumberland@yahoo.com> writes: > > 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) > > [snip] > > begin > > > -- Look to see if the login is already taken > > select * from user_info where login = client_login; > > > ERROR: unexpected SELECT query in exec_stmt_execsql() > > The error message isn't very helpful, but the issue here is that you're > doing a completely useless SELECT --- or at least plpgsql thinks it's > useless, because you're discarding the result. (I think that plpgsql is > being overly anal-retentive about it, since such a query might indeed be > useful if you then examine FOUND or ROW_COUNT, but that's the issue at > the moment.) Try making it a SELECT INTO instead. > > regards, tom lane __________________________________________________ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/
Lonnie Cumberland <lonnie_cumberland@yahoo.com> writes:
> Actually I have the need at different places in my functions to
> sometimes work on the list items returned from a query and also have
> the need to just work on the first result returned by a query.
SELECT ... LIMIT 1 will serve the second need. For the first, perhaps
use the FOR ... loop construct in plpgsql.
regards, tom lane
Lonnie, Tom:
Here's a somewhat complicated example of what Tom's talking about from
my own programs.
HOWEVER, you should use this kind of structure sparingly, if at all.
SQL is a declarative language, rather than a procedural one. For
updates to groups of records, you should put the updates in sets and use
declarative statements whenever possible, rather than a looping
structure.
If you find you *have* to do a lot of looping rather than taking a
declarative approach, you might want to consider changing languages.
PL/perl, PL/TCL and C are all much better equipped to handle loops and
arrays than PL/pgSQL and SQL.
The example, part of a 279-line function which calculates a weighted job
score evaluation for an HR database:
--Calculate DETAILS modifier
--loop through details one at a time, adding to candidates
--that have that detail
count_odetails := count_details - COALESCE(count_rdetails, 0);
IF count_odetails > 0 THEN insert_loop := 0; WHILE insert_loop < count_odetails
LOOP
SELECT detail_id INTO detail_no FROM order_details
WHEREorder_usq = v_order AND detail_req = FALSE ORDER BY detail_id LIMIT 1
OFFSETinsert_loop;
insert_loop := insert_loop + 1;
UPDATE matches SET match_score = match_score +
(20::NUMERIC/CAST(count_detailsAS
NUMERIC)) FROM candidate_details WHERE candidate_details.candidate_usq =
matches.candidate_usq AND match_sq = match_id AND detail_id
=detail_no;
END LOOP; END IF;
(NOTE: OFFSET will not accept any math if set dynamically {as above}.
Thus, "LIMIT 1 OFFSET insert_loop + 1" will error).
-Josh
--
______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete
informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small
businesses fax 621-2533 and non-profit organizations. San Francisco
Thanks for the code snippet Josh,
I'll give this method a try as it is only for a simple listing and will not
have to do too many things special.
Cheers
Lonnie
--- Josh Berkus <josh@agliodbs.com> wrote:
> Lonnie, Tom:
>
> Here's a somewhat complicated example of what Tom's talking about from
> my own programs.
>
> HOWEVER, you should use this kind of structure sparingly, if at all.
> SQL is a declarative language, rather than a procedural one. For
> updates to groups of records, you should put the updates in sets and use
> declarative statements whenever possible, rather than a looping
> structure.
>
> If you find you *have* to do a lot of looping rather than taking a
> declarative approach, you might want to consider changing languages.
> PL/perl, PL/TCL and C are all much better equipped to handle loops and
> arrays than PL/pgSQL and SQL.
>
> The example, part of a 279-line function which calculates a weighted job
> score evaluation for an HR database:
>
> --Calculate DETAILS modifier
> --loop through details one at a time, adding to candidates
> --that have that detail
>
> count_odetails := count_details - COALESCE(count_rdetails, 0);
>
> IF count_odetails > 0 THEN
> insert_loop := 0;
> WHILE insert_loop < count_odetails LOOP
>
> SELECT detail_id INTO detail_no
> FROM order_details
> WHERE order_usq = v_order AND detail_req = FALSE
> ORDER BY detail_id
> LIMIT 1 OFFSET insert_loop;
>
> insert_loop := insert_loop + 1;
>
> UPDATE matches SET match_score = match_score +
> (20::NUMERIC/CAST(count_details AS
> NUMERIC))
> FROM candidate_details
> WHERE candidate_details.candidate_usq =
> matches.candidate_usq
> AND match_sq = match_id
> AND detail_id = detail_no;
>
> END LOOP;
> END IF;
>
> (NOTE: OFFSET will not accept any math if set dynamically {as above}.
> Thus, "LIMIT 1 OFFSET insert_loop + 1" will error).
>
> -Josh
>
> --
> ______AGLIO DATABASE SOLUTIONS___________________________
> Josh Berkus
> Complete information technology josh@agliodbs.com
> and data management solutions (415) 565-7293
> for law firms, small businesses fax 621-2533
> and non-profit organizations. San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
__________________________________________________
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail.
http://personal.mail.yahoo.com/