Обсуждение: Final stored procedure question, for now anyway

Ted Byers wrote:

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

In PgAdmin, make sure you are looking under the correct schema.  Each
schema has its own set of objects.

--
Guy Rouillier

----- Original Message -----

From: Jonel Rienton

To: 'Ted Byers' ; pgsql-general@postgresql.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: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ted Byers
Sent: Wednesday, December 28, 2005 11:13 AM
To: pgsql-general@postgresql.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
http://www.randddecisionsupportsolutions.com/

----- Original Message -----

From: Jonel Rienton

To: 'Ted Byers' ; pgsql-general@postgresql.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: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ted Byers
Sent: Wednesday, December 28, 2005 11:13 AM
To: pgsql-general@postgresql.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
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

----- Original Message -----

From: Jonel Rienton

To: 'Ted Byers' ; pgsql-general@postgresql.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.ted.byers@rogers.com]
Sent: Wednesday, December 28, 2005 1:45 PM
To: Jonel Rienton; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Final stored procedure question, for now anyway

Did you find a fix for it?

----- Original Message -----

From: Jonel Rienton

To: 'Ted Byers' ; pgsql-general@postgresql.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: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ted Byers
Sent: Wednesday, December 28, 2005 11:13 AM
To: pgsql-general@postgresql.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
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

----- Original Message -----

From: Jonel Rienton

To: 'Ted Byers' ; pgsql-general@postgresql.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.ted.byers@rogers.com]
Sent: Wednesday, December 28, 2005 1:45 PM
To: Jonel Rienton; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Final stored procedure question, for now anyway

Did you find a fix for it?

----- Original Message -----

From: Jonel Rienton

To: 'Ted Byers' ; pgsql-general@postgresql.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: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ted Byers
Sent: Wednesday, December 28, 2005 11:13 AM
To: pgsql-general@postgresql.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
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

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

Do you refresh you database after you add the function?  It will not
show up until after you refresh.

Ted Byers wrote:

> 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@rientongroup.com>
>     *To:* 'Ted Byers' <mailto:r.ted.byers@rogers.com> ;
>     pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.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.ted.byers@rogers.com]
>     *Sent:* Wednesday, December 28, 2005 1:45 PM
>     *To:* Jonel Rienton; pgsql-general@postgresql.org
>     <mailto:pgsql-general@postgresql.org>
>     *Subject:* Re: [GENERAL] Final stored procedure question, for now
>     anyway
>
>     Did you find a fix for it?
>
>         ----- Original Message -----
>         *From:* Jonel Rienton <mailto:jonel@RientonGroup.com>
>         *To:* 'Ted Byers' <mailto:r.ted.byers@rogers.com> ;
>         pgsql-general@postgresql.org
>         <mailto:pgsql-general@postgresql.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:* pgsql-general-owner@postgresql.org
>         <mailto:pgsql-general-owner@postgresql.org>
>         [mailto:pgsql-general-owner@postgresql.org] *On Behalf Of *Ted
>         Byers
>         *Sent:* Wednesday, December 28, 2005 11:13 AM
>         *To:* pgsql-general@postgresql.org
>         <mailto:pgsql-general@postgresql.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
>         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
>

----- Original Message -----
From: "Frank L. Parks" <fparks@ezbizpartner.com>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, December 28, 2005 7:27 PM
Subject: [Bulk] Re: [GENERAL] Final stored procedure question, for now
anyway


> Do you refresh you database after you add the function?  It will not show
> up until after you refresh.
>
I didn't realise that refreshing the display in pgAdmin would not be enough.
I did that several times with no luck.  I then closed the connection to the
database and then connected again, and voila!  The functions appear,
entirely in lower case, in the public schema in my EntityDatabase.  This
database has two schemas, "public", which was there the moment I created the
database, and "People" which I created.

Now, this begs more questions.  My function references tables in the
"People" schema.  They do not qualify the references to these tables.  In my
view, the function properly belongs to the people schema.

1) Can the function be simply moved from the public schema to the people
schema, or do I need to delete the function and recreate it within the
people schema?  If the latter is true, how would I specify, within the SQL,
that the function is to be added to the People schema instead of public?

2) Do I need to qualify my references to my tables in the function to
identify the schema in which the tables exist, or will Postgres find the
right tables with the function in its present form?  If I have to further
qualify the table references, what is the correct qualification of the table
references within the SQL in the function definition?

Thanks,

Ted

Please see below

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ted Byers
Sent: Wednesday, December 28, 2005 7:29 PM
To: Frank L. Parks; pgsql-general@postgresql.org
Subject: Re: [Bulk] Re: [GENERAL] Final stored procedure question, for now
anyway


----- Original Message -----
From: "Frank L. Parks" <fparks@ezbizpartner.com>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, December 28, 2005 7:27 PM
Subject: [Bulk] Re: [GENERAL] Final stored procedure question, for now
anyway


> Do you refresh you database after you add the function?  It will not
> show up until after you refresh.
>
I didn't realise that refreshing the display in pgAdmin would not be enough.

I did that several times with no luck.  I then closed the connection to the
database and then connected again, and voila!  The functions appear,
entirely in lower case, in the public schema in my EntityDatabase.  This
database has two schemas, "public", which was there the moment I created the
database, and "People" which I created.

Now, this begs more questions.  My function references tables in the
"People" schema.  They do not qualify the references to these tables.  In my
view, the function properly belongs to the people schema.

1) Can the function be simply moved from the public schema to the people
schema, or do I need to delete the function and recreate it within the
people schema?  If the latter is true, how would I specify, within the SQL,
that the function is to be added to the People schema instead of public?

    CREATE FUNCTION schema_name.function_name ....

2) Do I need to qualify my references to my tables in the function to
identify the schema in which the tables exist, or will Postgres find the
right tables with the function in its present form?  If I have to further
qualify the table references, what is the correct qualification of the table
references within the SQL in the function definition?

    schema_name.table_name

Thanks,

Ted



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

I had to change the datatype of a column from text to integer. The column
contained integers (obviously stored as text).

When I tried to change the datatype of the column I got an error message
saying that the column can not be cast to integer:

Operation : ALTER TABLE "public"."subjectgroupcondition"  ALTER COLUMN
"param1" TYPE INTEGER
Result    : "ERROR:  column "param1" cannot be cast to type
"pg_catalog.int4""

However when I created an other integer column in the table and updated it
from the text column there was no problem casting the data:
Operation : UPDATE public.subjectgroupcondition SET param2 = cast(param1 as
integer);
Result    : "OK."

Since pg knows that it should cast the data and it can cast it I think I
should have been able to change the datatype in the first instance. Maybe
this behaviour has a good reason but I don't know what it is.

Regards,
SWK

On Thu, Dec 29, 2005 at 09:46:10PM +0100, Klein Balzs wrote:
> I had to change the datatype of a column from text to integer. The column
> contained integers (obviously stored as text).
>
> When I tried to change the datatype of the column I got an error message
> saying that the column can not be cast to integer:
>
> Operation : ALTER TABLE "public"."subjectgroupcondition"  ALTER COLUMN
> "param1" TYPE INTEGER
> Result    : "ERROR:  column "param1" cannot be cast to type
> "pg_catalog.int4""

Use the USING clause:

ALTER TABLE subjectgroupcondition
  ALTER COLUMN param1 TYPE integer USING param1::integer;

> However when I created an other integer column in the table and updated it
> from the text column there was no problem casting the data:
> Operation : UPDATE public.subjectgroupcondition SET param2 = cast(param1 as
> integer);
> Result    : "OK."
>
> Since pg knows that it should cast the data and it can cast it I think I
> should have been able to change the datatype in the first instance. Maybe
> this behaviour has a good reason but I don't know what it is.

Some casts can be done implicitly and some not.  For more information
see the CREATE CAST and Type Conversion documentation:

http://www.postgresql.org/docs/8.1/interactive/sql-createcast.html
http://www.postgresql.org/docs/8.1/interactive/typeconv.html

--
Michael Fuhr