Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from
	<pgsql-general-owner+M197639=pgsql-general-arkari@postgresql.org>)
	id 1UhgtQ-0007MH-Oz for pgsql-general@arkaria.postgresql.org;
	Wed, 29 May 2013 13:58:21 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.72) (envelope-from
	<pgsql-general-owner+M197639=pgsql-general-arkari@postgresql.org>)
	id 1UhgtQ-0001F4-4u for pgsql-general@arkaria.postgresql.org;
	Wed, 29 May 2013 13:58:20 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <rr.rosas@gmail.com>)
	id 1UhgtN-0001DL-Cn; Wed, 29 May 2013 13:58:17 +0000
Received: from mail-yh0-x235.google.com ([2607:f8b0:4002:c01::235])
	by makus.postgresql.org with esmtp (Exim 4.80)
	(envelope-from <rr.rosas@gmail.com>)
	id 1UhgtK-0001nf-HH; Wed, 29 May 2013 13:58:16 +0000
Received: by mail-yh0-f53.google.com with SMTP id f64so1168447yha.26
	for <multiple recipients>; Wed, 29 May 2013 06:58:13 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=message-id:date:from:user-agent:mime-version:to:subject
	:content-type:content-transfer-encoding;
	bh=lt1jAmt7wZebi2Jd1e6mgjmW69D3VTPL9C6/KJnP/ZE=;
	b=ASxcOdGKOP4Mt2OY9eTPhhtrkbJm035i9TaKKl3Uzoclm7f7qcIUivHRyNxh8Sbjrg
	oWIXlduY1BT2U4UFod5NMtaH0QO/MLLp8wfSaWR1FErzlXfwcB4wUDw6RzQxqueO3r+e
	gnMDiWZ1DRm0c5QGQLmhC2t6PoISmVMwaRB1lHjeyycei12DgAWT9kHRhMXBPCUbpR6g
	A+Vp023hkwXmPw8wgPFtUmK2jE2r2G12b+NBVh+7B8qa+HRGsCMiW2V83cRWfvKTfBrl
	Vr8Zv14llVpPLiDcuRvv2H+t5sJVLK96hjb6J9ZC/qJ+1MZtN2JRBHJ1xERktWk/FgaO
	7GLg==
X-Received: by 10.236.114.107 with SMTP id b71mr1074018yhh.94.1369835893807;
	Wed, 29 May 2013 06:58:13 -0700 (PDT)
Received: from [192.168.2.3] ([179.217.1.154])
	by mx.google.com with ESMTPSA id z65sm52596732yhc.9.2013.05.29.06.58.11
	for <multiple recipients>
	(version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
	Wed, 29 May 2013 06:58:12 -0700 (PDT)
Message-ID: <51A60971.8060608@gmail.com>
Date: Wed, 29 May 2013 10:58:09 -0300
From: Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:17.0) Gecko/20130518 Icedove/17.0.5
MIME-Version: 1.0
To: pgsql-sql@postgresql.org, pgsql-general@postgresql.org
Subject: foreign key to multiple tables depending on another column's value
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: 0.7 (/)
List-Archive: <http://archives.postgresql.org/pgsql-general>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-general.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@postgresql.org>
List-Post: <mailto:pgsql-general@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-general>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
X-Mailing-List: pgsql-general
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org

Sorry, I wasn't sure what list I should be sending this question to...

I have a multi-tenant-like application. We have a fields tree that we 
call a template, with something like this:

fields(id, parent_id, name)

And several other related tables. Since we started to support multiple 
templates we created another schema per added template, each having the 
same fields and related tables. Now I was asked to create another 
special template that doesn't contain the values for those fields 
(stored in separate tables in the regular schemas) and that would allow 
us to map some field to some specific field on each of the aggregate 
templates.

So, to exemplify, let's suppose we have regular templates "template1" 
and "template2" and a special template "special1". This is what I want 
to achieve:

I'll use the name (id, parent_id) notation.

template1 (table name is template1.fields):

- Target (1, null)
   - Name (2, 1)
   - Country (3, 1)


template2 (table name is template2.fields):

- Borrower (1, null)
   - Name (12, 1)
   - Country (13, 1)

special1 (table name is special1.fields):

- Company [maps to either Target or Borrower] (20, null)
  - Name (21, 20)
  - Country (22, 20)

So, now the idea is to map those fields using another table:

special1.mapped_fields(field_id, template_name, mapped_field_id), with 
records like:

(20, 'template1', 1)
(20, 'template2', 1)
(21, 'template1', 2)
(21, 'template2', 12)
(22, 'template1', 3)
(22, 'template2', 13)

But the problem, as you have noticed is that I can't use a foreign key 
with mapped_field_id, because the referenced table will depend on the 
value of template_name.

I know I could use a trigger, or some check constraint maybe, to ensure 
the field exists upon insert (or update), but I can't ensure the 
database will become inconsistent in case I remove a mapped field from 
the other schema.

Now I can finally explain my question: is it possible that I set some 
sort of foreign key whose referenced table and column would depend on 
the value of another column?

Thanks in advance,
Rodrigo.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general