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
);