----- Original Message -----
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
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
