Re: First-class Polymorphic joins? - Mailing list pgsql-general

From Jony Cohen
Subject Re: First-class Polymorphic joins?
Date
Msg-id CAD9xk1_iagxm-nWX+4btuqhiZeBKkbkuM41TySsmUdvPTtCqXg@mail.gmail.com
Whole thread Raw
In response to Re: First-class Polymorphic joins?  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general
Hi, 
You can do this today using inheritance.
define a table "tagable" with person & blog as child tables.

than you could run queries like:
select * from tags JOIN tagable on (tag_id = tag_fk);
 tag_id |   tag_desc   | id | tag_fk |          data
--------+--------------+----+--------+------------------------
      3 | blog tag 1   |  1 |      3 | blog tagged by tag 1
      2 | person tag 2 |  2 |      2 | person tagged by tag 2

simple :)

Regards,
 - Jony


On Fri, Aug 14, 2015 at 4:04 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 08/13/2015 05:59 PM, Guyren Howe wrote:

Ccing list

On Aug 13, 2015, at 17:49 , Adrian Klaver <adrian.klaver@aklaver.com> wrote:

A polymorphic join is where a fk contains not just an id but an indicator of which table it refers to.

I am pretty sure it already does that:

http://www.postgresql.org/docs/9.4/interactive/sql-createtable.html

REFERENCES reftable [ ( refcolumn ) ]

I apologize for not being clearer.

The point is that the fk in different rows can reference different tables. I might want to be able to attach a tag to a person or a blog post, say. And then I want to find all the persons and blog posts with a particular tag, in a single query.

Could you just not turn that around?:

tag
  tag_id
  tag_desc

person
  person_id
  tag_fk references tag
       
blog
  blog_id
  tag_fk references tag


The simplest implementation is to have a table reference as a first-class value I can store in a field.



--
Adrian Klaver
adrian.klaver@aklaver.com


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

pgsql-general by date:

Previous
From: "Wayne E. Seguin"
Date:
Subject: Re: [BDR] vs pgpool-II v3
Next
From: Mikhail
Date:
Subject: How to get cursor query