Re: [Bulk] Re: [GENERAL] Final stored procedure question, for now anyway
Ted Byers wrote: > > 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? PostgreSQL uses a search path to find DB objects. The default search path is a schema with the same name you used to connect, followed by the "public" schema. You can alter this search path. So you have several options: (1) hard-code a schema name onto each table in your queries (which I would never do and do not suggest), (2) put your DB objects (tables, functions, etc) into the schema that you use to connect, or (3) alter your search path. As much as I like PG and as powerful as it is, I'm surprised the designers have elected not to implement synonyms, which is how most other databases address this issue. -- Guy Rouillier ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [Bulk] Re: [GENERAL] Final stored procedure question, for now anyway
Please see below -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> To: 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [Bulk] Re: [GENERAL] Final stored procedure question, for now anyway
- Original Message - From: "Frank L. Parks" <[EMAIL PROTECTED]> To: 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
