[SQL] foreign key to multiple tables depending on another column's value
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-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] reduce many loosely related rows down to one
> SELECT id, > (array_agg(rspid))[1] AS rspid,-- (1) for such cases, I have created an new aggregate function: SELECT firstnotnull(rspid) AS rspid, this avoid to collect first all rspid values to then keep only the first one... CREATE OR REPLACE FUNCTION public.first_agg_nn ( anyelement, anyelement ) RETURNS anyelement AS $$ SELECT $1; $$ LANGUAGE SQL IMMUTABLE STRICT COST 1; CREATE AGGREGATE public.firstnotnull ( sfunc= public.first_agg_nn, basetype = anyelement, stype= anyelement ); regards, Marc Mamin > -Original Message- > From: [email protected] [mailto:pgsql-sql- > [email protected]] On Behalf Of Torsten Grust > Sent: Dienstag, 28. Mai 2013 17:08 > To: [email protected] > Subject: Re: [SQL] reduce many loosely related rows down to one > > On 25 May 2013, at 9:19, Bill MacArthur wrote (with possible > deletions): > > [...] > > select * from test; > > > > id | rspid | nspid | cid | iac | newp | oldp | ppv | tppv > > +---+---+-+-+--+--+-+- > > 1 | 2 | 3 | 4 | t | | | | > > 1 | 2 | 3 | | | 100 | | | > > 1 | 2 | 3 | | | | 200 | | > > 1 | 2 | 3 | | | | | | 4100.00 > > 1 | 2 | 3 | | | | | | 3100.00 > > 1 | 2 | 3 | | | | | -100.00 | > > 1 | 2 | 3 | | | | | 250.00 | > > 2 | 7 | 8 | 4 | | | | | > > (8 rows) > > > > -- I want this result (where ppv and tppv are summed and the other > > distinct values are boiled down into one row) > > -- I want to avoid writing explicit UNIONs that will break if, say > the > > "cid" was entered as a discreet row from the row containing "iac" > > -- in this example "rspid" and "nspid" are always the same for a > given > > ID, however they could possibly be absent for a given row as well > > > > id | rspid | nspid | cid | iac | newp | oldp | ppv | tppv > > +---+---+-+-+--+--+-+- > > 1 |2 | 3 | 4 | t | 100 | 200 | 150.00 | 7200.00 > > 2 |7 | 8 | 4 | | | |0.00 |0.00 > > One possible option could be > > SELECT id, > (array_agg(rspid))[1] AS rspid,-- (1) > (array_agg(nspid))[1] AS nspid, > (array_agg(cid))[1] AS cid, > bool_or(iac) AS iac, -- (2) > max(newp) AS newp, -- (3) > min(oldp) AS oldp, -- (4) > coalesce(sum(ppv), 0) AS ppv, > coalesce(sum(tppv),0) AS tppv > FROM test > GROUP BY id; > > > This query computes the desired output for your example input. > > There's a caveat here: your description of the problem has been > somewhat vague and it remains unclear how the query should respond if > the functional dependency id -> rspid does not hold. In this case, the > array_agg(rspid)[1] in the line marked (1) will pick one among many > different(!) rspid values. > I don't know your scenario well enough to judge whether this would be > an acceptable behavior. Other possible behaviors have been implemented > in the lines (2), (3), (4) where different aggregation functions are > used to reduce sets to a single value (e.g., pick the largest/smallest > of many values ...). > > Cheers, >--Torsten > > > -- > | Torsten "Teggy" Grust > | [email protected] > > > -- > Sent via pgsql-sql mailing list ([email protected]) To make > changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Re: [GENERAL] foreign key to multiple tables depending on another column's value
On Wed, May 29, 2013 at 9:58 AM, Rodrigo Rosenfeld Rosas wrote: > 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? > The FK tests are basically triggers, but highly optimized. That said, the way they enforce the integrity is by having a trigger on both tables. So for your custom need here, you would want to put a trigger on the referenced table to disallow deleting a value that is still referenced, or do whatever appropriate action upon delete/update your application needs.
[SQL] Re: [GENERAL] foreign key to multiple tables depending on another column's value
Em 29-05-2013 12:51, Vick Khera escreveu: On Wed, May 29, 2013 at 9:58 AM, Rodrigo Rosenfeld Rosas mailto:[email protected]>> wrote: 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? The FK tests are basically triggers, but highly optimized. That said, the way they enforce the integrity is by having a trigger on both tables. So for your custom need here, you would want to put a trigger on the referenced table to disallow deleting a value that is still referenced, or do whatever appropriate action upon delete/update your application needs. Ok, thanks. I just wanted to be sure there wasn't some hidden feature of PostgreSQL I wasn't aware of yet... You know, I'm always learning something new on PG, so it worths trying to ask first ;) Cheers, Rodrigo.
