NOTIFY with tuples

Hi,

I've used LISTEN/NOTIFY in a few apps with great success (both
the new and the old implementation) but I've found myself
wondering why I couldn't use a richer payload, and wondered if
anyone already had plans in this direction.

It seems there are number of academic and commercial
systems (StreamSQL [1], CQL [2], ...) which provide powerful
queryable streams of tuples, including windowing, grouping,
joining and pipelining facilities, all of which are far beyond
what I have been picturing.

I imagine a very simple system like this, somehow built on top of
the existing NOTIFY infrastructure:

CREATE STREAM foo (sensor INTEGER, temperature NUMERIC);

In session A:

INSERT INTO foo VALUES (42, 99.0);
INSERT INTO foo VALUES (99, 100.0);
COMMIT;

Meanwhile in session B:

SELECT * FROM foo;

And perhaps even some simple filtering:

SELECT * FROM foo WHERE sensor = 42;

I don't know how you would first signal your interest in foo
before you can start SELECTing from it... perhaps with LISTEN.

I suppose running the SELECT query on a stream would return only
tuples that are queued up and ready to fetch, without blocking to
wait for more, and a client could execute the query repeatedly,
using select() on the file descriptor to wait for data to be
ready (the same way people currently wait between calls to
PGnotifies).

As for implementation details, I haven't done much research yet
into how something like this would be done and am very new to the
source tree, but I thought I'd present this idea and see if it's
a duplicate effort, or someone has a much better idea, or it is
instantly shot down in flames for technical or other reasons,
before investing further in it.

Thanks!

Thomas Munro

[1] http://en.wikipedia.org/wiki/StreamSQL
[2] http://de.wikipedia.org/wiki/Continuous_Query_Language