Skip site navigation (1) Skip section navigation (2)

Re: Using a function in different schemas

From: Juan Manuel Alvarez <naicigam(at)gmail(dot)com>
To: Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Using a function in different schemas
Date: 2011-08-31 18:37:51
Message-ID: CADj1=jTrr0EU5z3_7H8oKDrAjJRtv13suzV+f7_BJHmYoyA44w@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
Thanks Adrian! That is exactly what I was looking for! =o)

On Wed, Aug 31, 2011 at 3:35 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:
> On 08/31/2011 08:38 AM, Juan Manuel Alvarez wrote:
>>
>> Hello everyone! This is the first time I post on this list. Until now,
>> I was able to get a solution to all the problems I had, but now I am
>> facing an issue I can't resolve, so I hope you can help me.
>>
>> The problem goes like this: I am using the same distribution of
>> tables/functions into different schemas, so I have the following
>> layout
>> - "schema1" has tables "myTable1" and "myTable2", and function
>> "myFunction"
>> - "schema2" has tables "myTable1" and "myTable2", and function
>> "myFunction"
>>
>> Until now I used the full names to distinguish between schemas, like
>> this: schema1.myTable1 and schema2.myTable1.
>> But now I have to add an update trigger in both "myTable1" that calls
>> to a function that makes a select statement on "myTable2".
>> The problem is that even if the trigger is inside "mySchema1", it
>> can't find "myTable2" and I don't know how to get the schema that the
>> function is into.
>>
>> Things I have tried so far:
>> - current_schema() but, according to the documentation, it "returns
>> the name of the schema that is first in the search path" and that is
>> neither "mySchema1" or "mySchema2".
>> - Passing a parameter to the trigger: since it is an update trigger I
>> haven't found a way to pass parameter to it.
>>
>> Can anyone please point me in the right direction?
>
> Assuming using plpgsql look here:
> http://www.postgresql.org/docs/9.0/interactive/plpgsql-trigger.html
>
> In particular:
>
> "
> TG_TABLE_SCHEMA
>
>    Data type name; the name of the schema of the table that caused the
> trigger invocation.
> "
>
>>
>> Thanks in advance!
>> Juan M.
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com
>

In response to

pgsql-general by date

Next:From: Ben ChobotDate: 2011-08-31 18:53:39
Subject: Re: regression between 8.4.8 and 8.4.2?
Previous:From: Adrian KlaverDate: 2011-08-31 18:35:53
Subject: Re: Using a function in different schemas

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group