Skip site navigation (1) Skip section navigation (2)

Re: Final stored procedure question, for now anyway

From: "Jonel Rienton" <jonel(at)rientongroup(dot)com>
To: "'Ted Byers'" <r(dot)ted(dot)byers(at)rogers(dot)com>,<pgsql-general(at)postgresql(dot)org>
Subject: Re: Final stored procedure question, for now anyway
Date: 2005-12-29 00:19:50
Message-ID: 002f01c60c0d$959e5e50$04000100@aspire (view raw or flat)
Thread:
Lists: pgsql-general
then check what the other fellow suggested, make sure you're creating the
function in the right database and the right schema; and checking it in
pgAdmin at the same location.
 
cheers,
jonel
 

   _____  

From: Ted Byers [mailto:r(dot)ted(dot)byers(at)rogers(dot)com] 
Sent: Wednesday, December 28, 2005 6:15 PM
To: Jonel Rienton; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Final stored procedure question, for now anyway


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: HYPERLINK "mailto:jonel(at)rientongroup(dot)com"Jonel Rienton 
To: HYPERLINK "mailto:r(dot)ted(dot)byers(at)rogers(dot)com"'Ted Byers' ; HYPERLINK
"mailto:pgsql-general(at)postgresql(dot)org"pgsql-general(at)postgresql(dot)org 
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(dot)ted(dot)byers(at)rogers(dot)com] 
Sent: Wednesday, December 28, 2005 1:45 PM
To: Jonel Rienton; HYPERLINK
"mailto:pgsql-general(at)postgresql(dot)org"pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Final stored procedure question, for now anyway


Did you find a fix for it?

----- Original Message ----- 
From: HYPERLINK "mailto:jonel(at)RientonGroup(dot)com"Jonel Rienton 
To: HYPERLINK "mailto:r(dot)ted(dot)byers(at)rogers(dot)com"'Ted Byers' ; HYPERLINK
"mailto:pgsql-general(at)postgresql(dot)org"pgsql-general(at)postgresql(dot)org 
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: HYPERLINK
"mailto:pgsql-general-owner(at)postgresql(dot)org"pgsql-general-owner(at)postgresql(dot)or
g [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Ted Byers
Sent: Wednesday, December 28, 2005 11:13 AM
To: HYPERLINK
"mailto:pgsql-general(at)postgresql(dot)org"pgsql-general(at)postgresql(dot)org
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
HYPERLINK
"http://www.randddecisionsupportsolutions.com/"http://www.randddecisionsuppo
rtsolutions.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



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

In response to

pgsql-general by date

Next:From: Frank L. ParksDate: 2005-12-29 00:27:56
Subject: Re: Final stored procedure question, for now anyway
Previous:From: Ted ByersDate: 2005-12-29 00:14:41
Subject: Re: Final stored procedure question, for now anyway

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group