Horton

Re: [PATCH] Support for foreign keys with arrays

Tom Lane <tgl@sss.pgh.pa.us>

View thread
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Noah Misch <noah@leadboat.com>
Cc: Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>, PostgreSQL-development <pgsql-hackers@postgresql.org>, Marco Nenciarini <marco.nenciarini@2ndQuadrant.it>
Date: 2011-11-17T05:08:32Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Allow LEAKPROOF functions for better performance of security views.

  2. Improve labeling of pg_test_fsync open_sync test output.

Noah Misch <noah@leadboat.com> writes:
> On Fri, Nov 04, 2011 at 01:48:02PM +0100, Gabriele Bartolini wrote:
>> CREATE TABLE pt (
>> id INTEGER PRIMARY KEY,
>> 
>> CREATE TABLE ft (
>> id SERIAL PRIMARY KEY,
>> pids INTEGER[] REFERENCES pt,

> I'm assuming the SQL spec says nothing about a feature like this?

I'm pretty certain that the SQL spec flat out forbids this.

The least we could do is invent some non-spec syntax that makes the
intention clear, rather than having the system assume that an error case
was intended to mean something else.  Maybe

	pids INTEGER[] ARRAY REFERENCES pt,

or something like that.  (ARRAY is a fully reserved word already,
so I think this syntax should work, but I've not tried it.)

BTW, has anyone thought through whether this is a sane idea at all?
It seems to me to be full of cases that will require rather arbitrary
decisions, like whether ON DELETE CASCADE should involve deleting the
whole row or just one array element.

			regards, tom lane