Thread: How to determine table schema in trigger function

I created generic (for tables in different schemas) trigger function :

CREATE OR REPLACE FUNCTION setlastchange() RETURNS "trigger"
AS $$BEGIN
UPDATE serverti SET lastchange='now'  WHERE tablename=TG_RELNAME and
   schemaname=TG_SCHEMA;
RETURN NULL;
END$$  LANGUAGE plpgsql STRICT;


Unfortunately, this does not work since TG_SCHEMA is not valid in PL/pgSQL

How to determine schema name where table TG_RELNAME belongs in trigger
function ?

Andrus.

table structure is:

CREATE TABLE serverti (
  schemaname CHARACTER(7),
  tablename CHARACTER(8) ,
  lastchange timestamp,
  primary key (schemaname, tablename) );

On Thu, Aug 18, 2005 at 05:02:58PM +0300, Andrus wrote:
> How to determine schema name where table TG_RELNAME belongs in trigger
> function ?

You could use TG_RELID to query the system catalogs.  See the
documentation for pg_class and pg_namespace.

--
Michael Fuhr

On Thu, 2005-08-18 at 17:02 +0300, Andrus wrote:
> I created generic (for tables in different schemas) trigger function :
>
> CREATE OR REPLACE FUNCTION setlastchange() RETURNS "trigger"
> AS $$BEGIN
> UPDATE serverti SET lastchange='now'  WHERE tablename=TG_RELNAME and
>    schemaname=TG_SCHEMA;
> RETURN NULL;
> END$$  LANGUAGE plpgsql STRICT;
>
>
> Unfortunately, this does not work since TG_SCHEMA is not valid in PL/pgSQL
>
> How to determine schema name where table TG_RELNAME belongs in trigger
> function ?

How about extracting relnamespace from pg_catalog.pg_class?

  UPDATE serverti SET lastchange='now'
    WHERE tablename=TG_RELNAME and schemaname=(
            SELECT  n.nspname
              FROM  pg_catalog.pg_namespace AS n,
                    pg_catalog.pg_class AS c
              WHERE c.relnamespace = n.oid AND
                    c.oid = TG_RELID
         );