Re: Internal design of MERGE, with Rules

[Sam Mason <sam(at)samason(dot)me(dot)uk>]

On Wed, Apr 30, 2008 at 04:58:52PM +0100, Simon Riggs wrote:
> That means we probably need to introduce new infrastructure in the tcop
> or executor modules to handle queries-within-queries. This isn't
> special-casing MERGE so much as introducing infrastructure for a new
> class of query, such as MERGE, REPLACE, INSERT ELSE UPDATE. (Merge
> itself does cover almost all cases of this type of query, but we'd be
> able to fairly easily support all of the different syntax).
> 
> MERGE would then be represented by a query that has many "side
> queries" (so called so we don't confused calling them sub-queries).

Why make them special cases?  (I'm sure there's a good reason!)

I've sometimes wanted to be able to put DML statements inside SELECT
statements.  The following is a slightly reasonable example:

  INSERT INTO ilog (i,ts,n)
    SELECT i, now(), COUNT(*)
    FROM (
      INSERT INTO bar (x,y)
        SELECT 5, n
        FROM baz
        WHERE i = 10
        RETURNING i) x
    GROUP BY i;

Hum, that implies a nasty schema (ilog.ts should be in bar).  Ah well, I
hope you get the idea.  The simplest example I can think of is:

  SELECT * FROM (INSERT INTO foo (n) VALUES (1) RETURNING 1) x;

Hum, I think I may have just thought of why.  What would:

  SELECT (INSERT INTO foo (n) VALUES (f.n) RETURNING 1)
  FROM foo f;

be expected to do?  I'm thinking of nasty cases where the outer code
reading foo never stops because there's new stuff being inserted ahead
of it.  But then again, you can put the insert into a stored procedure
and it does indeed do something sensible.


  Sam