From: | "Ted Byers" <r(dot)ted(dot)byers(at)rogers(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Final stored procedure question, for now anyway |
Date: | 2005-12-28 17:12:59 |
Message-ID: | 033101c60bd1$f4223230$6401a8c0@RnDworkstation |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | Jim C. Nasby | 2005-12-28 17:33:02 | Re: POSTGRES DB 3 800 000 rows table, speed up? |
Previous Message | Dmitry Panov | 2005-12-28 17:11:44 | Re: WAL logs multiplexing? |