Обсуждение: How to find owning schema in function

Поиск
Список
Период
Сортировка

How to find owning schema in function

От
"Andrus"
Дата:
8.1+ database contains separate schemas for every company named company1,
company2, .... companyi.

order tables in those schemas contain trigger like for company1:

CREATE OR REPLACE FUNCTION dok_seq_trig() RETURNS "trigger"
AS $$BEGIN
IF NEW.tasudok IS NULL AND NEW.doktyyp!='O'  THEN
NEW.tasudok = nextval( 'company1.'|| TG_RELNAME || '_'|| NEW.doktyyp
||'_seq');
END IF;

IF NEW.arvenumber IS NULL AND NEW.doktyyp='O'  THEN
NEW.arvenumber = nextval( 'company1.'|| TG_RELNAME || '_'|| NEW.doktyyp
||'_seq');
END IF;

RETURN NEW;
END$$  LANGUAGE plpgsql STRICT;

This code has hard coded schema name 'company1'  . If new company schema n
is created from existing one, trigger functions needs manual update to
change schema to companyn.

How to change this code so that instead of hard-coded schema name it
automatically uses the schema where trigger function is defined ?

Re: How to find owning schema in function

От
Sim Zacks
Дата:
On 11/05/2011 05:36 PM, Andrus wrote: <blockquote cite="mid:590C7236EDF84F6CB786F6C7678A7167@dell2" type="cite">8.1+
databasecontains separate schemas for every company named company1, company2, .... companyi. <br /><br /> order tables
inthose schemas contain trigger like for company1: <br /><br /> CREATE OR REPLACE FUNCTION dok_seq_trig() RETURNS
"trigger"<br /> AS $$BEGIN <br /> IF NEW.tasudok IS NULL AND NEW.doktyyp!='O'  THEN <br /> NEW.tasudok = nextval(
'company1.'||TG_RELNAME || '_'|| NEW.doktyyp ||'_seq'); <br /> END IF; <br /><br /> IF NEW.arvenumber IS NULL AND
NEW.doktyyp='O' THEN <br /> NEW.arvenumber = nextval( 'company1.'|| TG_RELNAME || '_'|| NEW.doktyyp ||'_seq'); <br />
ENDIF; <br /><br /> RETURN NEW; <br /> END$$  LANGUAGE plpgsql STRICT; <br /><br /> This code has hard coded schema
name'company1'  . If new company schema n is created from existing one, trigger functions needs manual update to change
schemato companyn. <br /><br /> How to change this code so that instead of hard-coded schema name it automatically uses
theschema where trigger function is defined ? <br /></blockquote><a
href="http://www.postgresql.org/docs/8.2/interactive/plpgsql-trigger.html">http://www.postgresql.org/docs/8.2/interactive/plpgsql-trigger.html</a><br
/>Shows all available variables. In this case - <span class="Apple-style-span" style="color: rgb(0, 0, 0); font-family:
   monospace; font-size: 13px; font-style: normal; font-variant:     normal; font-weight: normal; letter-spacing:
normal;line-height:     normal; orphans: 2; text-align: left; text-indent: 0px;     text-transform: none; white-space:
normal;widows: 2;     word-spacing: 0px; background-color: rgb(255, 255, 255);">TG_TABLE_SCHEMA</span><br /><br />
Sim<br/>