From: Gnanavel S <s.gnanavel@gmail.com>
To: Lathika Wijerathne <lathika@rezgateway.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: User Define Functions
Date: Wed, 27 Jul 2005 12:38:49 +0530
Message-ID: <eec3b03c0507270008513803c2@mail.gmail.com> (raw)
In-Reply-To: <20050726224915.55373F6D@dm16.mta.everyone.net>
References: <20050726224915.55373F6D@dm16.mta.everyone.net>
use
set search_path = yourschemaname
to set the schema before calling the function
On 7/27/05, Lathika Wijerathne <lathika@rezgateway.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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: s.gnanavel@gmail.com, lathika@rezgateway.com
Subject: Re: User Define Functions
In-Reply-To: <eec3b03c0507270008513803c2@mail.gmail.com>
References: <20050726224915.55373F6D@dm16.mta.everyone.net> <eec3b03c0507270008513803c2@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox