Thread: PL/pgSQL Trigger - Variable for shema? Like TG_RELNAME

Hello,

while my trigger is fired, I can access the variable "TG_RELNAME" to get the
name of the table that is updated.
How will I get the name of the shema this table is stored?

Thank you,

Christian

am  31.10.2005, um 10:47:50 +0100 mailte Christian Hofmann folgendes:
> Hello,
>
> while my trigger is fired, I can access the variable "TG_RELNAME" to get the
> name of the table that is updated.
> How will I get the name of the shema this table is stored?

You have the TG_RELNAME:

SELECT nspname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relname = TG_RELNAME;


>
> Thank you,

Thanks to itsMe_ in #postgresql ;-)
(IRC-Channel)

11:32 < akretschmer> Hi, i have a table-oid and need to know the schema-name. What can i do?
11:43 < itsMe_> akretschmer: SELECT nspname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE
c.relname= 
                'tablename';



HTH, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===

Hello Anreas,

> You have the TG_RELNAME:
>
> SELECT nspname FROM pg_class c JOIN pg_namespace n ON n.oid =
> c.relnamespace WHERE c.relname = TG_RELNAME;

Thank you. But when I habe more than one table in different shemas with the
same name?

> 11:32 < akretschmer> Hi, i have a table-oid and need to know
> the schema-name. What can i do?
> 11:43 < itsMe_> akretschmer: SELECT nspname FROM pg_class c
> JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relname =
>                 'tablename';

Yes, I will take the table-oid to get the shema name. Thats a better idea.

I thought there were a variable the shema is stored in. So I need an extra
query.

But it is ok.

Thank you,

Christian

On Mon, Oct 31, 2005 at 12:04:17PM +0100, Christian Hofmann wrote:
> > You have the TG_RELNAME:
> >
> > SELECT nspname FROM pg_class c JOIN pg_namespace n ON n.oid =
> > c.relnamespace WHERE c.relname = TG_RELNAME;
>
> Thank you. But when I habe more than one table in different shemas with the
> same name?

Use "WHERE c.oid = TG_RELID" instead of "WHERE c.relname = TG_RELNAME".

--
Michael Fuhr