Creating Functions in Separate Schema

[Mark Dexter]

Our application currently runs in Microsoft SQL Server and we are
porting it over to Postgres. When we write enbedded SQL queries for SQL
Server, we need to reference user-defined functions with the prefix
"dbo." (e.g., "dbo.Function_Name()"). In experimenting with Postgres,
it appears that we can create a schema called "dbo" and then reference
functions with the same syntax (e.g., "dbo.Function_Name()").

We're trying to understand whether there will be any disadvantages to
doing this. Are there any possible issues or problems with this
approach? Any help will be greatly appreciated. Thanks.

[Tino Wildenhain]

Hi Mark,

Am Do, den 26.08.2004 schrieb Mark Dexter um 23:42:

Our application currently runs in Microsoft SQL Server and we are
porting it over to Postgres. When we write enbedded SQL queries for
SQL Server, we need to reference user-defined functions with the
prefix "dbo." (e.g., "dbo.Function_Name()"). In experimenting with
Postgres, it appears that we can create a schema called "dbo" and then
reference functions with the same syntax (e.g.,
"dbo.Function_Name()").

We're trying to understand whether there will be any disadvantages to
doing this. Are there any possible issues or problems with this
approach? Any help will be greatly appreciated. Thanks.

This is generally not a problem. The search order
depends on the current user, so if there is a schema
with same name as the current user, its the first to search
in (and the one to put new objects in) but if you
always specify the schema name with your objects you
should not expect any problems and can use as many
schemas you want.

Regards
Tino Wildenhain
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Richard Huxton]

Mark Dexter wrote:

Our application currently runs in Microsoft SQL Server and we are
porting it over to Postgres. When we write enbedded SQL queries for SQL
Server, we need to reference user-defined functions with the prefix
"dbo." (e.g., "dbo.Function_Name()"). In experimenting with Postgres,
it appears that we can create a schema called "dbo" and then reference
functions with the same syntax (e.g., "dbo.Function_Name()").

We're trying to understand whether there will be any disadvantages to
doing this. Are there any possible issues or problems with this
approach? Any help will be greatly appreciated. Thanks.

Only that you'll need to be careful if you have two functions with the
same name in different schemas. If you have dbo.fn() and public.fn() and
call fn() then which you get will depend on your search_path.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Oliver Elphick]

On Thu, 2004-08-26 at 22:42, Mark Dexter wrote:

Our application currently runs in Microsoft SQL Server and we are
porting it over to Postgres. When we write enbedded SQL queries for
SQL Server, we need to reference user-defined functions with the
prefix "dbo." (e.g., "dbo.Function_Name()"). In experimenting with
Postgres, it appears that we can create a schema called "dbo" and then
reference functions with the same syntax (e.g.,
"dbo.Function_Name()").

We're trying to understand whether there will be any disadvantages to
doing this. Are there any possible issues or problems with this
approach? Any help will be greatly appreciated. Thanks.

It should work fine.

If you set the search_path to include dbo, the "dbo." prefix would
become optional.

CREATE SCHEMA dbo;

CREATE FUNCTION dbo.function() ...

SELECT dbo.function() ...

SET SEARCH_PATH TO '...,dbo,public';

SELECT function() ...

Oliver Elphick
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend