Re: [SQL] 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_titleALIAS FOR $1;
first_name ALIAS FOR $2;
middle_name ALIAS FOR $3;
last_name ALIAS FOR $4;
email_address ALIAS FOR $5;
company_nameALIAS FOR $6;
client_loginALIAS 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,''0'');
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/
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [SQL] Calling plSQL functions
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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] >and data management solutions (415) 565-7293 > for law firms, small businessesfax 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/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Calling plSQL functions
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_titleALIAS FOR $1; > first_name ALIAS FOR $2; > middle_name ALIAS FOR $3; > last_name ALIAS FOR $4; > email_address ALIAS FOR $5; > company_nameALIAS FOR $6; > client_loginALIAS 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 information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] RE: Re: select substr???
Thanks, interesting. I did not find any mention of regular expressions in the 7.0.2 docs I installed locally. BTW, your code does not do exactly the same, as it removes any whitespace while the other one only collapses consecutive blanks. But, of course, regular expressions in PL/pgSQL make this much easier. As a further aside, I think that in both versions of the function the check for `$1 IS NULL' is not necessary; I got the impression that passing NULL as an argument to a function will automatically return NULL as the result, doesn't it? Albert. On Tue, Apr 10, 2001 at 05:41:26PM -0500, Jeff Eckermann wrote: > Regular expressions make this much easier. The below could be shortened to: > > create function ComparisonString(text) returns text as ' > declare > t alias for $1; > r text; > c char; > begin > if t is null or t !~ ''[^a-zA-Z0-9]'' > then > return t; > end if; > r = ; > for i in 1 .. char_length(t) loop > c = substring(t from i for 1); > if c ~ ''[a-zA-Z0-9]'' > then > r = r || c; > end if; > end loop; > return r; > end; > ' language 'plpgsql' with (IsCachable); > > > -Original Message- > > From: Albert REINER [SMTP:[EMAIL PROTECTED]] ... > > > > somewhere I use the following, which might be adapted to do what you > > want. I am sure there are more elegant ways of doing this, though. > > > > create function ComparisonString(text) returns text as ' > > declare > > t text; > > r text; > > c char; > > ns bool; > > begin > > if $1 is null then > > return NULL; > > end if; > > t = lower(trim(both $1)); > > r = ; > > ns = false; > > for i in 1 .. char_length(t) loop > > c = substring(t from i for 1); > > if c = '' '' then > > if ns then > > r = r || '' ''; > > end if; > > ns = false; > > else > > if position(c in ''abcdefghijklmnopqrstuvwxyz0123456789'') > 0 > > then > > r = r || c; > > ns = true; > > end if; > > end if; > > end loop; > > return trim(both r); > > end; > > ' language 'plpgsql' with (IsCachable); > > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Full outer join
Looking at the docs for 7.1, it appears that full outer joins are supported: http://www.postgresql.org/devel-corner/docs/user/queries.html However, I keep getting this error, and I don't know what it means: ERROR: FULL JOIN is only supported with mergejoinable join conditions I've tried several variations and keep getting the same error. Anyone have any ideas? Details below. Thanks, Tim SELECT * FROM (stats_http_downloads sh FULL OUTER JOIN stats_ftp_downloads sf USING (day,filerelease_id)) ; ERROR: FULL JOIN is only supported with mergejoinable join conditions SELECT day,filerelease_id FROM (stats_http_downloads sh FULL OUTER JOIN stats_ftp_downloads sf USING (day,filerelease_id)) ; ERROR: FULL JOIN is only supported with mergejoinable join conditions alexandria=# \d stats_http_downloads Table "stats_http_downloads" Attribute| Type | Modifier +-+-- day| integer | not null default '0' filerelease_id | integer | not null default '0' group_id | integer | not null default '0' downloads | integer | not null default '0' alexandria=# \d stats_ftp_downloads Table "stats_ftp_downloads" Attribute| Type | Modifier +-+-- day| integer | not null default '0' filerelease_id | integer | not null default '0' group_id | integer | not null default '0' downloads | integer | not null default '0' -- Founder - PHPBuilder.com / Geocrawler.com Lead Developer - SourceForge VA Linux Systems ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Function overlaps_interval
Folks, I wrote this PL/pgSQL function for my current project, and thought it would be generally useful. An expansion of the builtin overlaps(dt1,dt2,dt3,dt4) function, this function returns the interval of time for which the two datetime ranges overlap. Roberto, please include this in your online PL/pgSQL function library. CREATE FUNCTION overlap_interval(DATETIME, DATETIME, DATETIME, DATETIME) RETURNS INTERVAL AS ' DECLARE begin1 ALIAS for $1; end1 ALIAS for $2; begin2 ALIAS for $3; end2 ALIAS for $4; overlap_amount INTERVAL; BEGIN --test for overlap using the ovelap function. --if not found, return 0 interval. IF NOT overlaps(begin1, end1, begin2, end2) THEN RETURN ''00:00:00''::INTERVAL; END IF; --otherwise, test for the various forms of overlap IF begin1 < begin2 THEN IF end1 < end2 THEN overlap_amount := end1 - begin2; ELSE overlap_amount := end2 - begin2; END IF; ELSE IF end1 < end2 THEN overlap_amount := end1 - begin1; ELSE overlap_amount := end2 - begin1; END IF; END IF; RETURN overlap_amount; END;' LANGUAGE 'plpgsql'; -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Triggers on SELECT
Hello All, I have been reading on the uses of the Triggers from within the PL/pgSQL language and have seen that you can set up triggers for such things as UPDATE, and INSERT. I was just wondering why there was no trigger mechanicism for the SELECT statement? I could find some possible uses for this type of trigger as well. Cheers, Lonnie __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] DROP TABLE in transaction
Hello. I was wondering if anybody could explain to me why I can't roll back dropping a table. I would think that of all the events that should be rollback-able, dropping a table would be the first on the list. -- Dave ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Function overlaps_interval
On Thu, Apr 12, 2001 at 11:23:28AM -0700, Josh Berkus wrote: > Folks, > > I wrote this PL/pgSQL function for my current project, and thought it > would be generally useful. An expansion of the builtin > overlaps(dt1,dt2,dt3,dt4) function, this function returns the interval > of time for which the two datetime ranges overlap. > > Roberto, please include this in your online PL/pgSQL function library. Done! Thanks Josh! Date/Time manipulation functions are now our most popular category. How about you? Do you have a (SQL|PL/pgSQL|PL/Perl|PL/Tcl|PL/Python) function that you are proud of? A function that makes your life easier? Then waste no more time! Point your browser to http://www.brasileiro.net/postgres submit your recipe and join the CookBook Hall of Fame! You get your own awesome-cool "PostgreSQL Powered" button! We've been getting many hits from techdocs.postgresql.org, which shows that many are going to techdocs looking for solutions to common problems. Very useful. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Bad command or file name. Go sit in corner. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: Triggers on SELECT
On Thu, 12 Apr 2001, Lonnie Cumberland wrote: > Hello All, > > I have been reading on the uses of the Triggers from within the PL/pgSQL > language and have seen that you can set up triggers for such things as UPDATE, > and INSERT. > > I was just wondering why there was no trigger mechanicism for the SELECT > statement? You could use a RULE instead -- you might be able to get what you want this way. -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] RE: RE: Re: select substr???
The references to REs in the docs are a little hard to find (maybe it's just me). Bruce's book gives a good coverage, with the benefit of an index to find where that is. AFAICT, the implementation is "extended regular expressions", like egrep. My choice of characters to match was a little arbitrary: it would depend on the specification. You are correct, the check for "$1 is null" is not required. I was attempting an optimisation, as in "don't do anything else if this is null". The gain would depend on how much further processing the function would attempt before recognizing that it was dealing with a null value, which is something that I don't know enough to tell. > -Original Message- > From: Albert REINER [SMTP:[EMAIL PROTECTED]] > Sent: Thursday, April 12, 2001 10:44 AM > To: [EMAIL PROTECTED] > Subject: Re: RE: Re: select substr??? > > Thanks, interesting. I did not find any mention of regular expressions > in the 7.0.2 docs I installed locally. > > BTW, your code does not do exactly the same, as it removes any > whitespace while the other one only collapses consecutive blanks. But, > of course, regular expressions in PL/pgSQL make this much easier. > > As a further aside, I think that in both versions of the function the > check for `$1 IS NULL' is not necessary; I got the impression that > passing NULL as an argument to a function will automatically return > NULL as the result, doesn't it? > > Albert. > > > On Tue, Apr 10, 2001 at 05:41:26PM -0500, Jeff Eckermann wrote: > > Regular expressions make this much easier. The below could be shortened > to: > > > > create function ComparisonString(text) returns text as ' > > declare > > t alias for $1; > > r text; > > c char; > > begin > > if t is null or t !~ ''[^a-zA-Z0-9]'' > >then > > return t; > > end if; > > r = ; > > for i in 1 .. char_length(t) loop > > c = substring(t from i for 1); > > if c ~ ''[a-zA-Z0-9]'' > > then > > r = r || c; > > end if; > > end loop; > > return r; > > end; > > ' language 'plpgsql' with (IsCachable); > > > > > -Original Message- > > > From: Albert REINER [SMTP:[EMAIL PROTECTED]] > ... > > > > > > somewhere I use the following, which might be adapted to do what you > > > want. I am sure there are more elegant ways of doing this, though. > > > > > > create function ComparisonString(text) returns text as ' > > > declare > > > t text; > > > r text; > > > c char; > > > ns bool; > > > begin > > > if $1 is null then > > > return NULL; > > > end if; > > > t = lower(trim(both $1)); > > > r = ; > > > ns = false; > > > for i in 1 .. char_length(t) loop > > > c = substring(t from i for 1); > > > if c = '' '' then > > > if ns then > > > r = r || '' ''; > > > end if; > > > ns = false; > > > else > > > if position(c in ''abcdefghijklmnopqrstuvwxyz0123456789'') > 0 > > > then > > > r = r || c; > > > ns = true; > > > end if; > > > end if; > > > end loop; > > > return trim(both r); > > > end; > > > ' language 'plpgsql' with (IsCachable); > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] DROP TABLE in transaction
David Olbersen writes: > I was wondering if anybody could explain to me why I can't roll back dropping a > table. Because DROP TABLE removes the table file on disk, and you can't roll back that. Actually, in 7.1 you can. ;-) > I would think that of all the events that should be rollback-able, > dropping a table would be the first on the list. Naah. Insert and update are first. ;-) -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] DROP TABLE in transaction
On Thu, 12 Apr 2001, Peter Eisentraut wrote: > Because DROP TABLE removes the table file on disk, and you can't roll back > that. Actually, in 7.1 you can. ;-) Well I understand that it's being taken from the disk, but why does that action have to be done *right now*? Why can't it be postponed until I type 'commit;' ? I wonder how much time this addition would have saved those of us who type quickly and use the tab-completion too much :) -- Dave ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Re: DROP TABLE in transaction
On Thu, 12 Apr 2001, David Olbersen wrote: > On Thu, 12 Apr 2001, Peter Eisentraut wrote: > > > Because DROP TABLE removes the table file on disk, and you can't roll back > > that. Actually, in 7.1 you can. ;-) > > Well I understand that it's being taken from the disk, but why does that action > have to be done *right now*? > Why can't it be postponed until I type 'commit;' ? > > I wonder how much time this addition would have saved those of us who type > quickly and use the tab-completion too much :) If one were inclined to do this sort of thing, it might even make sense to argue that DROP TABLE hides the table (sets an attrib so that it doesn't show, query planner doesn't see it, etc.); it should actually be removed from disk when the database on VACUUM. -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] enumerating rows
Kovacs Zoltan writes: > Maybe a trivial question, maybe it's foreign from SQL, I'dont know... > How to add a column which stands for the row number in each row of the > result? E.g.: > > row_no | column1 | column2 | ... > ---+-+-+ ... > 1 | datum11 | datum12 | ... > 2 | datum21 | datum22 | ... >... | ... | ... | ... > > I didn't find anything in the docs. Your client can do that. When you fetch the results you have to have some sort of loop anyway, so you have (or keep) a counter there. There's no use of the server generating this information, because the numbering is implied by the order in which the rows are sent. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Re: Update
Kyle <[EMAIL PROTECTED]> writes: > It appears that the first function would get evaluated first under 7.0.3 > but the last function gets evaluated first under 7.1. Is that accurate? Actually, I was under the impression that (all else being equal) WHERE clauses would get evaluated right-to-left in 7.0.* as well. I was meaning to figure out where the clause order reversal is happening and undo it, but didn't get around to it for 7.1. > Is there a way to control which functions are given precidence? Nope, not at the moment. The code has remnants of a notion of cost of evaluation for functions, but it's not being used for anything ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Sql error
"comp" <[EMAIL PROTECTED]> writes: > but after connection when I type testdb=3D>\df it gives this error: > " ERROR: Function 'oid8types(oidvector)' does not exist. Use the version of psql that came with 7.0.3, not some older version. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
