Обсуждение: Using a function in different schemas
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? Thanks in advance! Juan M.
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.klaver@gmail.com
Thanks Adrian! That is exactly what I was looking for! =o) On Wed, Aug 31, 2011 at 3:35 PM, Adrian Klaver <adrian.klaver@gmail.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.klaver@gmail.com >