Thread: Creating Functions in Separate Schema

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. 

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

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

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