imported>Pavel v 7. 3. 2012, 22:07

2012-03-07T22:07:39Z

Nová stránka

Ukázka tabulkové funkce typu SFRM_Materialize

Funkce vrací celočíselnou řadu odpovídající indexům zadaného pole:
<pre>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;
}
</pre>
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ů):
<pre>
#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);
}
</pre>

Registrace:
<pre>
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;
</pre>

Použití:
<pre>
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)
</pre>

Iterátor pole - Historie editací

imported>Pavel v 7. 3. 2012, 22:07