Well, I just ran another test and the result indicates the cause of
the problem lies elsewhere. I replaced the original function name
with "add_entity" and the end result is the same. The function gets
stored somewhere, but it is not displayed anywhere by pgAdmin
Any suggestions?
----- Original Message -----
*From:* Jonel Rienton <mailto:jonel@xxxxxxxxxxxxxxxx>
*To:* 'Ted Byers' <mailto:r.ted.byers@xxxxxxxxxx> ;
pgsql-general@xxxxxxxxxxxxxx <mailto:pgsql-general@xxxxxxxxxxxxxx>
*Sent:* Wednesday, December 28, 2005 4:16 PM
*Subject:* Re: [GENERAL] Final stored procedure question, for now
anyway
yup, just use lowercase all the time
------------------------------------------------------------------------
*From:* Ted Byers [mailto:r.ted.byers@xxxxxxxxxx]
*Sent:* Wednesday, December 28, 2005 1:45 PM
*To:* Jonel Rienton; pgsql-general@xxxxxxxxxxxxxx
<mailto:pgsql-general@xxxxxxxxxxxxxx>
*Subject:* Re: [GENERAL] Final stored procedure question, for now
anyway
Did you find a fix for it?
----- Original Message -----
*From:* Jonel Rienton <mailto:jonel@xxxxxxxxxxxxxxxx>
*To:* 'Ted Byers' <mailto:r.ted.byers@xxxxxxxxxx> ;
pgsql-general@xxxxxxxxxxxxxx
<mailto:pgsql-general@xxxxxxxxxxxxxx>
*Sent:* Wednesday, December 28, 2005 1:24 PM
*Subject:* RE: [GENERAL] Final stored procedure question, for
now anyway
it's because of the mixed-case you're using in the function
name, i've had this issue last week myself and it seems to be
the culprit
------------------------------------------------------------------------
*From:* pgsql-general-owner@xxxxxxxxxxxxxx
<mailto:pgsql-general-owner@xxxxxxxxxxxxxx>
[mailto:pgsql-general-owner@xxxxxxxxxxxxxx] *On Behalf Of *Ted
Byers
*Sent:* Wednesday, December 28, 2005 11:13 AM
*To:* pgsql-general@xxxxxxxxxxxxxx
<mailto:pgsql-general@xxxxxxxxxxxxxx>
*Subject:* [GENERAL] Final stored procedure question, for now
anyway
OK, I finally have Postgres accepting my stored procedure.
here is the relevant data in the history window of pgAmind III
Query:
=================================
-- Executing query:
CREATE OR REPLACE FUNCTION addEntity (
fn IN VARCHAR,
ln IN VARCHAR,
ivar IN VARCHAR,
hi IN VARCHAR,
pw IN VARCHAR,
ea IN VARCHAR,
ad IN VARCHAR,
c IN VARCHAR,
p IN VARCHAR,
co IN VARCHAR,
pc IN VARCHAR
)
RETURNS void AS $$
DECLARE
varID INTEGER;
BEGIN
SELECT INTO varID uid from uids where email_address=ea;
IF varID IS NOT NULL THEN
INSERT INTO addy
(uid,address,city,province,country,postal_code)
VALUES (varID,ad,c,p,co,pc);
ELSE
INSERT INTO
uids(family_name,first_name,initials,hid,pword,email_address)
VALUES (ln,fn,ivar,hi,pw,ea);
INSERT INTO addy(uid,address,city,province,country,postal_code)
VALUES (currval('uids_uid_seq'),ad,c,p,co,pc);
END IF;
END;
$$ LANGUAGE 'plpgsql' VOLATILE;
Query returned successfully with no result in 0 ms.
=================================
So I now go into pgAmin III, and take a look, and I can't find it.
When I execute the above SQL, where will Postgres store the
function? I see, in pgAmin's main window, several places
where functions are stored, but none of them contain my function.
It must be stored somewhere since I get an error saying the
function already exists if I attempt to modiy it slightly and
re-run it. For example, I noticed I used the wrong sequence
in the last INSERT statement. To correct that, I had to add
"OR REPLACE" after "CREATE" to make the correction. I am
puzzled though that I can't find it when I go back to the
pgAmin main window and search through the whole database
(after pressing <F5> to refresh the contents of the windows).
Thanks to all who helped me on this matter.
Cheers,
Ted
R.E. (Ted) Byers, Ph.D., Ed.D.
R & D Decision Support Software
http://www.randddecisionsupportsolutions.com/
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.8/215 - Release Date:
12/27/2005
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.8/215 - Release Date:
12/27/2005
