On Thu, Aug 20, 2015 at 8:19 PM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:
SELECT [...]FROM (SELECT reference_id, [...] FROM table_where_referenced_id_is_a_pk WHERE reference_id EXISTS/IN/JOIN)srcLEFT JOIN type1 USING (reference_id)LEFT JOIN type2 USING (reference_id)[...]
there are no tables where reference_id is a pk, I could create one or do : select reference_id from ( values (..), (...), (...) .... )
the tricky part with the join (and where I was not clear about it in my original description) is that a reference_id can match in multiple tables (eg. it can be a fk in type1 and type2), so it then becomes a bit harder to collect all the common attributes and 'types' when doing joins like this.
For example let's assume there is a group_id to be be retrieved among all tables as a common attribute:
if reference_id was existing only in one table, I could do coalesce(type1.group_id, ... type5.group_id) as group_id in the main select
however that would not work in this case.
I could work around the common attributes however.
But for retrieving the types, what I really need to have as a return of this query is data that allows me to partition the reference_id for each type like:
type1 -> ref1, ref2, ref5
type2 -> ref1, ref3
type3 -> ref4, ref3
I guess I could try to return an array and fill it with case/when for each table eg. something like
ARRAY( CASE WHEN type1.id IS NOT NULL THEN 'type1' END, ... CASE WHEN type1.id IS NOT NULL THEN 'type5' END)
and then collect all the non-null values in the code.
Or consider whether PostgreSQL Inheritance would work - though basically its a friendly API over the "UNION ALL" query you proposed.
The problem with postgresql inheritance is that it would not play well with the orm and substantially complicates implementation.
Thanks for the all the ideas, that helps me a lot to brainstorm more.
