|
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] On Behalf Of Ted Byers Sent: Wednesday, December 28, 2005 11:13 AM To: 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/ |
