Re: User Define Functions

use
set search_path = yourschemaname
to set the schema before calling the function

On 7/27/05, Lathika Wijerathne <lathika(at)rezgateway(dot)com> wrote:
>
> Hi,
>
> I have created the database with 3 schemas.
>
> Example : Schema names : Newyorlk, Atlanta,California
>
> Each schema has a similar tables. But there can be schema specific tables
> too.
>
> I want to create a user define functions to retrieve information.
>
> ----------------------------------------------------------------------
>
> CREATE OR REPLACE FUNCTION Newyorlk.get_customer_name (custid integer,
> invoice integer)
>
> RETURNS varchar AS
>
> $$
>
> DECLARE
>
> m_display varchar (100);
>
> m_status varchar(10);
>
> BEGIN
>
> SELECT cname INTO m_display from Newyorlk.*CUSTOMER*
>
> WHERE id = custid;
>
> SELECT status INTO m_status from Newyorlk.*CUSTOMER_INVOICE*
>
> WHERE id = invoice;
>
> m_display := m_display||'-'|| m_status ;
>
> RETURN m_display;
>
> END;
>
> $$
>
> LANGUAGE 'plpgsql' VOLATILE;
>
> --------------------------------------------------------------------
>
> In the above function if I change Newyorlk.*CUSTOMER to CUSTOMER . *It
> gives a runtime error saying table not available.
>
> I think when I remove the schema name, posgresSql checks the table in the
> public schema.
>
> But in Oracle, when we give the schema name with the function name, it
> assumes all the objects within the functions are referring to that schema.
>
> Do you know any other way to do a user define function, without hard
> cording schema name each time when referring to a table.
>
> Thanks in Advance
>
> Lathika
>
>

--
with regards,
S.Gnanavel
Satyam Computer Services Ltd.