Iterátor pole

Ukázka tabulkové funkce typu SFRM_Materialize

Funkce vrací celočíselnou řadu odpovídající indexům zadaného pole:

Datum array_subscripts(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(array_subscripts);

Datum
array_subscripts(PG_FUNCTION_ARGS)
{
        ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
        ArrayType *v = PG_GETARG_ARRAYTYPE_P(0);
        TupleDesc       tupdesc;
        Tuplestorestate *tupstore;
        MemoryContext per_query_ctx;
        MemoryContext oldcontext;
        int reqdim;
        int     *lb, *dimv;
        int     lower, upper;
        bool reverse;
        int i;

       /* check to see if caller supports us returning a tuplestore */
        if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
                ereport(ERROR,
                                (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
                                 errmsg("set-valued function called in context that cannot accept a set")));
        if (!(rsinfo->allowedModes & SFRM_Materialize))
                ereport(ERROR,
                                (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
                                 errmsg("materialize mode required, but it is not " \
                                                "allowed in this context")));

        reqdim = (PG_NARGS() < 2) ? 1 : PG_GETARG_INT32(1);
        reverse = (PG_NARGS() < 3) ? false : PG_GETARG_BOOL(2);

        /* Sanity check: does it look like an array at all? */
        if (ARR_NDIM(v) <= 0 || ARR_NDIM(v) > MAXDIM)
                 PG_RETURN_NULL();

        /* Sanity check: was the requested dim valid */
        if (reqdim <= 0 || reqdim > ARR_NDIM(v))
                 PG_RETURN_NULL();

        lb = ARR_LBOUND(v);
        dimv = ARR_DIMS(v);

        lower = lb[reqdim - 1];
        upper = dimv[reqdim - 1] + lb[reqdim - 1] - 1;

        /* need to build tuplestore in query context */
        per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
        oldcontext = MemoryContextSwitchTo(per_query_ctx);


        /* note: for 8.4 and higher - tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc); */
        tupdesc = rsinfo->expectedDesc;
        tupstore = tuplestore_begin_heap(true, false, work_mem);

        for (i = lower; i <= upper; i++)
        {
                Datum        value;
                bool         null = false;
                HeapTuple       tuple;

                /* generate junk in short-term context */
                MemoryContextSwitchTo(oldcontext);

                value = !reverse ? Int32GetDatum(i) : Int32GetDatum(upper - i + 1);
                tuple = heap_form_tuple(tupdesc, &value, &null);

                MemoryContextSwitchTo(per_query_ctx);
                tuplestore_puttuple(tupstore, tuple);
        }

        /* clean up and return the tuplestore */
        tuplestore_donestoring(tupstore);

        MemoryContextSwitchTo(oldcontext);

        rsinfo->returnMode = SFRM_Materialize;
        rsinfo->setResult = tupstore;
        rsinfo->setDesc = tupdesc;

        return (Datum) 0;
}

a její obdoba typu SRFM_by_call (sice o zhruba 10% pomalejší zato paměťově šetrnější (má smysl řešit od 10 000 řádků):

#include "funcapi.h"

typedef struct generate_iterator_fctx
{
        int4    lower;
        int4    upper;
        bool    reverse;
} generate_iterator_fctx;

Datum array_subscripts(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(array_subscripts);

/* 
 * array_subscripts(array anyarray, dim int, reverse bool)
 */
Datum
array_subscripts(PG_FUNCTION_ARGS)
{
        FuncCallContext *funcctx;
        MemoryContext oldcontext;
        generate_iterator_fctx *fctx;

        /* stuff done only on the first call of the function */
        if (SRF_IS_FIRSTCALL())
        {
                ArrayType *v = PG_GETARG_ARRAYTYPE_P(0);
                int reqdim;
                int     *lb, *dimv;

                /* create a function context for cross-call persistence */
                funcctx = SRF_FIRSTCALL_INIT();

                reqdim = (PG_NARGS() < 2) ? 1 : PG_GETARG_INT32(1);

                /* Sanity check: does it look like an array at all? */
                if (ARR_NDIM(v) <= 0 || ARR_NDIM(v) > MAXDIM)
                        SRF_RETURN_DONE(funcctx);

                /* Sanity check: was the requested dim valid */
                if (reqdim <= 0 || reqdim > ARR_NDIM(v))
                        SRF_RETURN_DONE(funcctx);

                /*
                 * switch to memory context appropriate for multiple function calls
                 */
                oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
                fctx = (generate_iterator_fctx *) palloc(sizeof(generate_iterator_fctx));

                lb = ARR_LBOUND(v);
                dimv = ARR_DIMS(v);

                fctx->lower = lb[reqdim - 1];
                fctx->upper = dimv[reqdim - 1] + lb[reqdim - 1] - 1;
                fctx->reverse = (PG_NARGS() < 3) ? false : PG_GETARG_BOOL(2);

                funcctx->user_fctx = fctx;

                MemoryContextSwitchTo(oldcontext);
        }

        funcctx = SRF_PERCALL_SETUP();

        fctx = funcctx->user_fctx;

        if (fctx->lower <= fctx->upper)
        {
                if (!fctx->reverse)
                        SRF_RETURN_NEXT(funcctx, Int32GetDatum(fctx->lower++));
                else
                        SRF_RETURN_NEXT(funcctx, Int32GetDatum(fctx->upper--));
        }
        else
                /* do when there is no more left */
                SRF_RETURN_DONE(funcctx);
}

Registrace:

CREATE FUNCTION array_subscripts(anyarray)
RETURNS SETOF int
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT;

CREATE FUNCTION array_subscripts(anyarray, integer)
RETURNS SETOF int
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT;

CREATE FUNCTION array_subscripts(anyarray, integer, bool)
RETURNS SETOF int
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT;

Použití:

CREATE FUNCTION array_expand(anyarray)
RETURNS SETOF anyelements
AS
$$
  SELECT $1[i] 
     FROM array_subscripts($1) g(i);
$$ LANGUAGE SQL;

CREATE FUNCTION array_expand2(anyarray)
RETURNS SETOF anyelements
AS
$$
  SELECT $1[i][j] 
     FROM array_subscripts($1,1) d1(i),
          array_subscripts($1,2) d2{j};
$$ LANGUAGE SQL;

postgres=# select array_expand2(ARRAY[[10,11,12],[13,14,15]]);
 array_expand2 
---------------
            10
            11
            12
            13
            14
            15
(6 rows)