Re: Using Cursor in PostgreSQL 7.2

Tom Lane wrote:

> C functions returning sets are entirely possible, and there's even some
> documentation about how to do it in src/backend/utils/fmgr/README (which
> needs to be transposed to present tense and moved into the SGML docs,
> but it's better than nothing).
>
> There is at least one simple example in the 7.2 sources: see
> pg_stat_get_backend_idset() in src/backend/utils/adt/pgstatfuncs.c,
> and observe its usage in the pg_stat views, eg at the bottom of
> http://developer.postgresql.org/docs/postgres/monitoring-stats.html
>


It looks like the stats monitoring functions suffer from the same
limitation that I hit with dblink:


lt_lcat=# SELECT pg_stat_get_backend_pid(S.backendid) AS procpid,
pg_stat_get_backend_activity(S.backendid) AS current_query FROM (SELECT
pg_stat_get_backend_idset() AS backendid) AS S;
                        procpid | current_query
---------+---------------
    12713 |
    12762 |
(2 rows)


lt_lcat=# SELECT pg_stat_get_backend_pid(S.backendid) AS procpid,
pg_stat_get_backend_activity(S.backendid) AS current_query FROM (SELECT
pg_stat_get_backend_idset() AS backendid) AS S where
pg_stat_get_backend_pid(S.backendid) = 12713;
ERROR:  Set-valued function called in context that cannot accept a set


lt_lcat=# SELECT pg_stat_get_backend_pid(S.backendid) AS procpid,
pg_stat_get_backend_activity(S.backendid) AS current_query FROM (SELECT
pg_stat_get_backend_idset() AS backendid UNION ALL SELECT 1 WHERE FALSE)
AS S where pg_stat_get_backend_pid(S.backendid) = 12713;
  procpid | current_query
---------+---------------
    12713 |
(1 row)

The UNION is ugly but allows it to work. Tom discussed the reason this
is needed on: http://fts.postgresql.org/db/mw/msg.html?mid=120239.

Joe