agora inbox for [email protected]help / color / mirror / Atom feed
[PATCH] Covering GiST v10 28+ messages / 2 participants [nested] [flat]
* [PATCH] Covering GiST v10 @ 2019-01-30 00:59 Andreas Karlsson <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Andreas Karlsson @ 2019-01-30 00:59 UTC (permalink / raw) This patch allow adding INCLUDE colums to GiST index. These colums do not participate in GiST tree build, are not used for chosing subtree for inserts or splits. But they can be fetched during IndexOnlyScan. --- doc/src/sgml/ref/create_index.sgml | 4 +- doc/src/sgml/textsearch.sgml | 6 + src/backend/access/gist/gist.c | 33 +++- src/backend/access/gist/gistget.c | 4 +- src/backend/access/gist/gistscan.c | 12 +- src/backend/access/gist/gistsplit.c | 12 +- src/backend/access/gist/gistutil.c | 42 ++++-- src/include/access/gist_private.h | 2 + src/test/regress/expected/amutils.out | 4 +- src/test/regress/expected/index_including.out | 8 +- src/test/regress/expected/index_including_gist.out | 166 +++++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 + src/test/regress/sql/index_including.sql | 6 +- src/test/regress/sql/index_including_gist.sql | 90 +++++++++++ 15 files changed, 358 insertions(+), 34 deletions(-) create mode 100644 src/test/regress/expected/index_including_gist.out create mode 100644 src/test/regress/sql/index_including_gist.sql diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml index ad619cdcfe..f8657c6ae2 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -181,8 +181,8 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class= </para> <para> - Currently, only the B-tree index access method supports this feature. - In B-tree indexes, the values of columns listed in the + Currently, the B-tree and the GiST index access methods supports this + feature. In B-tree indexes, the values of columns listed in the <literal>INCLUDE</literal> clause are included in leaf tuples which correspond to heap tuples, but are not included in upper-level index entries used for tree navigation. diff --git a/doc/src/sgml/textsearch.sgml b/doc/src/sgml/textsearch.sgml index ecebade767..e4c75ebf6f 100644 --- a/doc/src/sgml/textsearch.sgml +++ b/doc/src/sgml/textsearch.sgml @@ -3675,6 +3675,12 @@ SELECT plainto_tsquery('supernovae stars'); </para> <para> + A GiST index can be covering, i.e. use the <literal>INCLUDE</literal> + clause. Included columns can have data types without any GiST operator + class. Included attributes will be stored uncompressed. + </para> + + <para> Lossiness causes performance degradation due to unnecessary fetches of table records that turn out to be false matches. Since random access to table records is slow, this limits the usefulness of GiST indexes. The diff --git a/src/backend/access/gist/gist.c b/src/backend/access/gist/gist.c index b75b3a8dac..2bbf7a7f49 100644 --- a/src/backend/access/gist/gist.c +++ b/src/backend/access/gist/gist.c @@ -75,7 +75,7 @@ gisthandler(PG_FUNCTION_ARGS) amroutine->amclusterable = true; amroutine->ampredlocks = true; amroutine->amcanparallel = false; - amroutine->amcaninclude = false; + amroutine->amcaninclude = true; amroutine->amkeytype = InvalidOid; amroutine->ambuild = gistbuild; @@ -1382,8 +1382,8 @@ gistSplit(Relation r, IndexTupleSize(itup[0]), GiSTPageSize, RelationGetRelationName(r)))); - memset(v.spl_lisnull, true, sizeof(bool) * giststate->tupdesc->natts); - memset(v.spl_risnull, true, sizeof(bool) * giststate->tupdesc->natts); + memset(v.spl_lisnull, true, sizeof(bool) * giststate->truncTupdesc->natts); + memset(v.spl_risnull, true, sizeof(bool) * giststate->truncTupdesc->natts); gistSplitByKey(r, page, itup, len, giststate, &v, 0); /* form left and right vector */ @@ -1462,8 +1462,19 @@ initGISTstate(Relation index) giststate->scanCxt = scanCxt; giststate->tempCxt = scanCxt; /* caller must change this if needed */ giststate->tupdesc = index->rd_att; + /* + * The truncated tupdesc does not contain the INCLUDE attributes. + * + * It is used to form tuples during tuple adjustement and page split. + * B-tree creates shortened tuple descriptor for every truncated tuple, + * because it is doing this less often: it does not have to form + * truncated tuples during page split. Also, B-tree is not adjusting + * tuples on internal pages the way GiST does. + */ + giststate->truncTupdesc = CreateTupleDescCopyConstr(index->rd_att); + giststate->truncTupdesc->natts = IndexRelationGetNumberOfKeyAttributes(index); - for (i = 0; i < index->rd_att->natts; i++) + for (i = 0; i < IndexRelationGetNumberOfKeyAttributes(index); i++) { fmgr_info_copy(&(giststate->consistentFn[i]), index_getprocinfo(index, i + 1, GIST_CONSISTENT_PROC), @@ -1531,6 +1542,20 @@ initGISTstate(Relation index) giststate->supportCollation[i] = DEFAULT_COLLATION_OID; } + for (; i < index->rd_att->natts; i++) + { + giststate->consistentFn[i].fn_oid = InvalidOid; + giststate->unionFn[i].fn_oid = InvalidOid; + giststate->compressFn[i].fn_oid = InvalidOid; + giststate->decompressFn[i].fn_oid = InvalidOid; + giststate->penaltyFn[i].fn_oid = InvalidOid; + giststate->picksplitFn[i].fn_oid = InvalidOid; + giststate->equalFn[i].fn_oid = InvalidOid; + giststate->distanceFn[i].fn_oid = InvalidOid; + giststate->fetchFn[i].fn_oid = InvalidOid; + giststate->supportCollation[i] = InvalidOid; + } + MemoryContextSwitchTo(oldCxt); return giststate; diff --git a/src/backend/access/gist/gistget.c b/src/backend/access/gist/gistget.c index a96ef5c3ac..a4ee8763f6 100644 --- a/src/backend/access/gist/gistget.c +++ b/src/backend/access/gist/gistget.c @@ -769,11 +769,13 @@ gistgetbitmap(IndexScanDesc scan, TIDBitmap *tbm) * * Opclasses that implement a fetch function support index-only scans. * Opclasses without compression functions also support index-only scans. + * Included attributes always can be fetched for index-only scans. */ bool gistcanreturn(Relation index, int attno) { - if (OidIsValid(index_getprocid(index, attno, GIST_FETCH_PROC)) || + if (attno > IndexRelationGetNumberOfKeyAttributes(index) || + OidIsValid(index_getprocid(index, attno, GIST_FETCH_PROC)) || !OidIsValid(index_getprocid(index, attno, GIST_COMPRESS_PROC))) return true; else diff --git a/src/backend/access/gist/gistscan.c b/src/backend/access/gist/gistscan.c index 78a8ede794..4fffc8b33a 100644 --- a/src/backend/access/gist/gistscan.c +++ b/src/backend/access/gist/gistscan.c @@ -158,6 +158,7 @@ gistrescan(IndexScanDesc scan, ScanKey key, int nkeys, if (scan->xs_want_itup && !scan->xs_hitupdesc) { int natts; + int nkeyatts; int attno; /* @@ -167,13 +168,22 @@ gistrescan(IndexScanDesc scan, ScanKey key, int nkeys, * types. */ natts = RelationGetNumberOfAttributes(scan->indexRelation); + nkeyatts = IndexRelationGetNumberOfKeyAttributes(scan->indexRelation); so->giststate->fetchTupdesc = CreateTemplateTupleDesc(natts); - for (attno = 1; attno <= natts; attno++) + for (attno = 1; attno <= nkeyatts; attno++) { TupleDescInitEntry(so->giststate->fetchTupdesc, attno, NULL, scan->indexRelation->rd_opcintype[attno - 1], -1, 0); } + + for (; attno <= natts; attno++) + { + /* taking opcintype from giststate->tupdesc */ + TupleDescInitEntry(so->giststate->fetchTupdesc, attno, NULL, + TupleDescAttr(so->giststate->tupdesc, attno - 1)->atttypid, + -1, 0); + } scan->xs_hitupdesc = so->giststate->fetchTupdesc; /* Also create a memory context that will hold the returned tuples */ diff --git a/src/backend/access/gist/gistsplit.c b/src/backend/access/gist/gistsplit.c index f210e0c39f..cf3af2821a 100644 --- a/src/backend/access/gist/gistsplit.c +++ b/src/backend/access/gist/gistsplit.c @@ -207,7 +207,7 @@ placeOne(Relation r, GISTSTATE *giststate, GistSplitVector *v, gistDeCompressAtt(giststate, r, itup, NULL, (OffsetNumber) 0, identry, isnull); - for (; attno < giststate->tupdesc->natts; attno++) + for (; attno < giststate->truncTupdesc->natts; attno++) { float lpenalty, rpenalty; @@ -485,7 +485,7 @@ gistUserPicksplit(Relation r, GistEntryVector *entryvec, int attno, GistSplitVec */ v->spl_dontcare = NULL; - if (attno + 1 < giststate->tupdesc->natts) + if (attno + 1 < giststate->truncTupdesc->natts) { int NumDontCare; @@ -657,7 +657,7 @@ gistSplitByKey(Relation r, Page page, IndexTuple *itup, int len, */ v->spl_risnull[attno] = v->spl_lisnull[attno] = true; - if (attno + 1 < giststate->tupdesc->natts) + if (attno + 1 < giststate->truncTupdesc->natts) gistSplitByKey(r, page, itup, len, giststate, v, attno + 1); else gistSplitHalf(&v->splitVector, len); @@ -683,7 +683,7 @@ gistSplitByKey(Relation r, Page page, IndexTuple *itup, int len, v->splitVector.spl_left[v->splitVector.spl_nleft++] = i; /* Compute union keys, unless outer recursion level will handle it */ - if (attno == 0 && giststate->tupdesc->natts == 1) + if (attno == 0 && giststate->truncTupdesc->natts == 1) { v->spl_dontcare = NULL; gistunionsubkey(giststate, itup, v); @@ -700,7 +700,7 @@ gistSplitByKey(Relation r, Page page, IndexTuple *itup, int len, * Splitting on attno column is not optimal, so consider * redistributing don't-care tuples according to the next column */ - Assert(attno + 1 < giststate->tupdesc->natts); + Assert(attno + 1 < giststate->truncTupdesc->natts); if (v->spl_dontcare == NULL) { @@ -771,7 +771,7 @@ gistSplitByKey(Relation r, Page page, IndexTuple *itup, int len, * that PickSplit (or the special cases above) produced correct union * datums. */ - if (attno == 0 && giststate->tupdesc->natts > 1) + if (attno == 0 && giststate->truncTupdesc->natts > 1) { v->spl_dontcare = NULL; gistunionsubkey(giststate, itup, v); diff --git a/src/backend/access/gist/gistutil.c b/src/backend/access/gist/gistutil.c index 8d3dfad27b..f7024eda12 100644 --- a/src/backend/access/gist/gistutil.c +++ b/src/backend/access/gist/gistutil.c @@ -160,7 +160,7 @@ gistMakeUnionItVec(GISTSTATE *giststate, IndexTuple *itvec, int len, evec = (GistEntryVector *) palloc((len + 2) * sizeof(GISTENTRY) + GEVHDRSZ); - for (i = 0; i < giststate->tupdesc->natts; i++) + for (i = 0; i < giststate->truncTupdesc->natts; i++) { int j; @@ -296,7 +296,7 @@ gistDeCompressAtt(GISTSTATE *giststate, Relation r, IndexTuple tuple, Page p, { int i; - for (i = 0; i < r->rd_att->natts; i++) + for (i = 0; i < IndexRelationGetNumberOfKeyAttributes(r); i++) { Datum datum; @@ -329,7 +329,7 @@ gistgetadjusted(Relation r, IndexTuple oldtup, IndexTuple addtup, GISTSTATE *gis gistDeCompressAtt(giststate, r, addtup, NULL, (OffsetNumber) 0, addentries, addisnull); - for (i = 0; i < r->rd_att->natts; i++) + for (i = 0; i < IndexRelationGetNumberOfKeyAttributes(r); i++) { gistMakeUnionKey(giststate, i, oldentries + i, oldisnull[i], @@ -442,7 +442,7 @@ gistchoose(Relation r, Page p, IndexTuple it, /* it has compressed entry */ zero_penalty = true; /* Loop over index attributes. */ - for (j = 0; j < r->rd_att->natts; j++) + for (j = 0; j < IndexRelationGetNumberOfKeyAttributes(r); j++) { Datum datum; float usize; @@ -470,7 +470,7 @@ gistchoose(Relation r, Page p, IndexTuple it, /* it has compressed entry */ result = i; best_penalty[j] = usize; - if (j < r->rd_att->natts - 1) + if (j < IndexRelationGetNumberOfKeyAttributes(r) - 1) best_penalty[j + 1] = -1; /* we have new best, so reset keep-it decision */ @@ -500,7 +500,7 @@ gistchoose(Relation r, Page p, IndexTuple it, /* it has compressed entry */ * If we looped past the last column, and did not update "result", * then this tuple is exactly as good as the prior best tuple. */ - if (j == r->rd_att->natts && result != i) + if (j == IndexRelationGetNumberOfKeyAttributes(r) && result != i) { if (keep_current_best == -1) { @@ -575,11 +575,13 @@ gistFormTuple(GISTSTATE *giststate, Relation r, Datum compatt[INDEX_MAX_KEYS]; int i; IndexTuple res; + /* currently we truncate tuples iif they are on internal pages */ + bool isTruncated = !isleaf; /* * Call the compress method on each attribute. */ - for (i = 0; i < r->rd_att->natts; i++) + for (i = 0; i < IndexRelationGetNumberOfKeyAttributes(r); i++) { if (isnull[i]) compatt[i] = (Datum) 0; @@ -602,7 +604,21 @@ gistFormTuple(GISTSTATE *giststate, Relation r, } } - res = index_form_tuple(giststate->tupdesc, compatt, isnull); + if (!isTruncated) + { + /* + * Emplace each included attribute. + */ + for (; i < r->rd_att->natts; i++) + { + if (isnull[i]) + compatt[i] = (Datum) 0; + else + compatt[i] = attdata[i]; + } + } + + res = index_form_tuple(isTruncated ? giststate->truncTupdesc : giststate->tupdesc, compatt, isnull); /* * The offset number on tuples on internal pages is unused. For historical @@ -644,7 +660,7 @@ gistFetchTuple(GISTSTATE *giststate, Relation r, IndexTuple tuple) bool isnull[INDEX_MAX_KEYS]; int i; - for (i = 0; i < r->rd_att->natts; i++) + for (i = 0; i < IndexRelationGetNumberOfKeyAttributes(r); i++) { Datum datum; @@ -679,6 +695,14 @@ gistFetchTuple(GISTSTATE *giststate, Relation r, IndexTuple tuple) fetchatt[i] = (Datum) 0; } } + + /* + * Get each included attribute. + */ + for (; i < r->rd_att->natts; i++) + { + fetchatt[i] = index_getattr(tuple, i + 1, giststate->tupdesc, &isnull[i]); + } MemoryContextSwitchTo(oldcxt); return heap_form_tuple(giststate->fetchTupdesc, fetchatt, isnull); diff --git a/src/include/access/gist_private.h b/src/include/access/gist_private.h index 3698942f9d..ccf06e676c 100644 --- a/src/include/access/gist_private.h +++ b/src/include/access/gist_private.h @@ -79,6 +79,8 @@ typedef struct GISTSTATE MemoryContext tempCxt; /* short-term context for calling functions */ TupleDesc tupdesc; /* index's tuple descriptor */ + TupleDesc truncTupdesc; /* truncated tuple descriptor + * for internal pages */ TupleDesc fetchTupdesc; /* tuple descriptor for tuples returned in an * index-only scan */ diff --git a/src/test/regress/expected/amutils.out b/src/test/regress/expected/amutils.out index 4570a39b05..d92a6d12c6 100644 --- a/src/test/regress/expected/amutils.out +++ b/src/test/regress/expected/amutils.out @@ -75,7 +75,7 @@ select prop, can_unique | f | | can_multi_col | t | | can_exclude | t | | - can_include | f | | + can_include | t | | bogus | | | (19 rows) @@ -159,7 +159,7 @@ select amname, prop, pg_indexam_has_property(a.oid, prop) as p gist | can_unique | f gist | can_multi_col | t gist | can_exclude | t - gist | can_include | f + gist | can_include | t gist | bogus | hash | can_order | f hash | can_unique | f diff --git a/src/test/regress/expected/index_including.out b/src/test/regress/expected/index_including.out index 16b4be34de..464629af88 100644 --- a/src/test/regress/expected/index_including.out +++ b/src/test/regress/expected/index_including.out @@ -313,22 +313,20 @@ SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; DROP TABLE tbl; /* - * 7. Check various AMs. All but btree must fail. + * 7. Check various AMs. All but btree and gist must fail. */ CREATE TABLE tbl (c1 int,c2 int, c3 box, c4 box); CREATE INDEX on tbl USING brin(c1, c2) INCLUDE (c3, c4); ERROR: access method "brin" does not support included columns -CREATE INDEX on tbl USING gist(c3) INCLUDE (c4); -ERROR: access method "gist" does not support included columns +CREATE INDEX on tbl USING gist(c3) INCLUDE (c1, c4); CREATE INDEX on tbl USING spgist(c3) INCLUDE (c4); ERROR: access method "spgist" does not support included columns CREATE INDEX on tbl USING gin(c1, c2) INCLUDE (c3, c4); ERROR: access method "gin" does not support included columns CREATE INDEX on tbl USING hash(c1, c2) INCLUDE (c3, c4); ERROR: access method "hash" does not support included columns -CREATE INDEX on tbl USING rtree(c1, c2) INCLUDE (c3, c4); +CREATE INDEX on tbl USING rtree(c3) INCLUDE (c1, c4); NOTICE: substituting access method "gist" for obsolete method "rtree" -ERROR: access method "gist" does not support included columns CREATE INDEX on tbl USING btree(c1, c2) INCLUDE (c3, c4); DROP TABLE tbl; /* diff --git a/src/test/regress/expected/index_including_gist.out b/src/test/regress/expected/index_including_gist.out new file mode 100644 index 0000000000..ed9906da66 --- /dev/null +++ b/src/test/regress/expected/index_including_gist.out @@ -0,0 +1,166 @@ +/* + * 1.1. test CREATE INDEX with buffered build + */ +-- Regular index with included columns +CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box); +-- size is chosen to exceed page size and trigger actual truncation +INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,8000) AS x; +CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c2,c3); +SELECT pg_get_indexdef(i.indexrelid) +FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid +WHERE i.indrelid = 'tbl_gist'::regclass ORDER BY c.relname; + pg_get_indexdef +----------------------------------------------------------------------------------- + CREATE INDEX tbl_gist_idx ON public.tbl_gist USING gist (c4) INCLUDE (c1, c2, c3) +(1 row) + +SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10)); + c1 | c2 | c3 | c4 +----+----+----+------------- + 1 | 2 | 3 | (2,3),(1,2) + 2 | 4 | 6 | (4,5),(2,3) + 3 | 6 | 9 | (6,7),(3,4) + 4 | 8 | 12 | (8,9),(4,5) +(4 rows) + +SET enable_bitmapscan TO off; +EXPLAIN (costs off) SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10)); + QUERY PLAN +------------------------------------------------ + Index Only Scan using tbl_gist_idx on tbl_gist + Index Cond: (c4 <@ '(10,10),(1,1)'::box) +(2 rows) + +SET enable_bitmapscan TO default; +DROP TABLE tbl_gist; +/* + * 1.2. test CREATE INDEX with inserts + */ +-- Regular index with included columns +CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box); +-- size is chosen to exceed page size and trigger actual truncation +CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c2,c3); +INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,8000) AS x; +SELECT pg_get_indexdef(i.indexrelid) +FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid +WHERE i.indrelid = 'tbl_gist'::regclass ORDER BY c.relname; + pg_get_indexdef +----------------------------------------------------------------------------------- + CREATE INDEX tbl_gist_idx ON public.tbl_gist USING gist (c4) INCLUDE (c1, c2, c3) +(1 row) + +SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10)); + c1 | c2 | c3 | c4 +----+----+----+------------- + 1 | 2 | 3 | (2,3),(1,2) + 2 | 4 | 6 | (4,5),(2,3) + 3 | 6 | 9 | (6,7),(3,4) + 4 | 8 | 12 | (8,9),(4,5) +(4 rows) + +SET enable_bitmapscan TO off; +EXPLAIN (costs off) SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10)); + QUERY PLAN +------------------------------------------------ + Index Only Scan using tbl_gist_idx on tbl_gist + Index Cond: (c4 <@ '(10,10),(1,1)'::box) +(2 rows) + +SET enable_bitmapscan TO default; +DROP TABLE tbl_gist; +/* + * 2. CREATE INDEX CONCURRENTLY + */ +CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box); +INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,10) AS x; +CREATE INDEX CONCURRENTLY tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c2,c3); +SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl_gist' ORDER BY indexname; + indexdef +----------------------------------------------------------------------------------- + CREATE INDEX tbl_gist_idx ON public.tbl_gist USING gist (c4) INCLUDE (c1, c2, c3) +(1 row) + +DROP TABLE tbl_gist; +/* + * 3. REINDEX + */ +CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box); +INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,10) AS x; +CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c3); +SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl_gist' ORDER BY indexname; + indexdef +------------------------------------------------------------------------------- + CREATE INDEX tbl_gist_idx ON public.tbl_gist USING gist (c4) INCLUDE (c1, c3) +(1 row) + +REINDEX INDEX tbl_gist_idx; +SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl_gist' ORDER BY indexname; + indexdef +------------------------------------------------------------------------------- + CREATE INDEX tbl_gist_idx ON public.tbl_gist USING gist (c4) INCLUDE (c1, c3) +(1 row) + +ALTER TABLE tbl_gist DROP COLUMN c1; +SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl_gist' ORDER BY indexname; + indexdef +---------- +(0 rows) + +DROP TABLE tbl_gist; +/* + * 4. Update, delete values in indexed table. + */ +CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box); +INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,10) AS x; +CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c3); +UPDATE tbl_gist SET c1 = 100 WHERE c1 = 2; +UPDATE tbl_gist SET c1 = 1 WHERE c1 = 3; +DELETE FROM tbl_gist WHERE c1 = 5 OR c3 = 12; +DROP TABLE tbl_gist; +/* + * 5. Alter column type. + */ +CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box); +INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,10) AS x; +CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c3); +ALTER TABLE tbl_gist ALTER c1 TYPE bigint; +ALTER TABLE tbl_gist ALTER c3 TYPE bigint; +\d tbl_gist + Table "public.tbl_gist" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + c1 | bigint | | | + c2 | integer | | | + c3 | bigint | | | + c4 | box | | | +Indexes: + "tbl_gist_idx" gist (c4) INCLUDE (c1, c3) + +DROP TABLE tbl_gist; +/* + * 6. EXCLUDE constraint. + */ +CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box, EXCLUDE USING gist (c4 WITH &&) INCLUDE (c1, c2, c3)); +INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,10) AS x; +ERROR: conflicting key value violates exclusion constraint "tbl_gist_c4_c1_c2_c3_excl" +DETAIL: Key (c4)=((4,5),(2,3)) conflicts with existing key (c4)=((2,3),(1,2)). +INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(3*x,2*x),point(3*x+1,2*x+1)) FROM generate_series(1,10) AS x; +EXPLAIN (costs off) SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10)); + QUERY PLAN +------------------------------------------------------------- + Index Only Scan using tbl_gist_c4_c1_c2_c3_excl on tbl_gist + Index Cond: (c4 <@ '(10,10),(1,1)'::box) +(2 rows) + +\d tbl_gist + Table "public.tbl_gist" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + c1 | integer | | | + c2 | integer | | | + c3 | integer | | | + c4 | box | | | +Indexes: + "tbl_gist_c4_c1_c2_c3_excl" EXCLUDE USING gist (c4 WITH &&) INCLUDE (c1, c2, c3) + +DROP TABLE tbl_gist; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index cc0bbf5db9..5684291ab0 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -55,7 +55,7 @@ test: copy copyselect copydml # ---------- test: create_misc create_operator create_procedure # These depend on the above two -test: create_index create_view index_including +test: create_index create_view index_including index_including_gist # ---------- # Another group of parallel tests diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 0c10c7100c..309ccec81a 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -63,6 +63,7 @@ test: create_operator test: create_procedure test: create_index test: index_including +test: index_including_gist test: create_view test: create_aggregate test: create_function_3 diff --git a/src/test/regress/sql/index_including.sql b/src/test/regress/sql/index_including.sql index ef5fd882f5..3f9b9b51d7 100644 --- a/src/test/regress/sql/index_including.sql +++ b/src/test/regress/sql/index_including.sql @@ -173,15 +173,15 @@ SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; DROP TABLE tbl; /* - * 7. Check various AMs. All but btree must fail. + * 7. Check various AMs. All but btree and gist must fail. */ CREATE TABLE tbl (c1 int,c2 int, c3 box, c4 box); CREATE INDEX on tbl USING brin(c1, c2) INCLUDE (c3, c4); -CREATE INDEX on tbl USING gist(c3) INCLUDE (c4); +CREATE INDEX on tbl USING gist(c3) INCLUDE (c1, c4); CREATE INDEX on tbl USING spgist(c3) INCLUDE (c4); CREATE INDEX on tbl USING gin(c1, c2) INCLUDE (c3, c4); CREATE INDEX on tbl USING hash(c1, c2) INCLUDE (c3, c4); -CREATE INDEX on tbl USING rtree(c1, c2) INCLUDE (c3, c4); +CREATE INDEX on tbl USING rtree(c3) INCLUDE (c1, c4); CREATE INDEX on tbl USING btree(c1, c2) INCLUDE (c3, c4); DROP TABLE tbl; diff --git a/src/test/regress/sql/index_including_gist.sql b/src/test/regress/sql/index_including_gist.sql new file mode 100644 index 0000000000..7d5c99b2e7 --- /dev/null +++ b/src/test/regress/sql/index_including_gist.sql @@ -0,0 +1,90 @@ +/* + * 1.1. test CREATE INDEX with buffered build + */ + +-- Regular index with included columns +CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box); +-- size is chosen to exceed page size and trigger actual truncation +INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,8000) AS x; +CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c2,c3); +SELECT pg_get_indexdef(i.indexrelid) +FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid +WHERE i.indrelid = 'tbl_gist'::regclass ORDER BY c.relname; +SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10)); +SET enable_bitmapscan TO off; +EXPLAIN (costs off) SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10)); +SET enable_bitmapscan TO default; +DROP TABLE tbl_gist; + +/* + * 1.2. test CREATE INDEX with inserts + */ + +-- Regular index with included columns +CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box); +-- size is chosen to exceed page size and trigger actual truncation +CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c2,c3); +INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,8000) AS x; +SELECT pg_get_indexdef(i.indexrelid) +FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid +WHERE i.indrelid = 'tbl_gist'::regclass ORDER BY c.relname; +SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10)); +SET enable_bitmapscan TO off; +EXPLAIN (costs off) SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10)); +SET enable_bitmapscan TO default; +DROP TABLE tbl_gist; + +/* + * 2. CREATE INDEX CONCURRENTLY + */ +CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box); +INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,10) AS x; +CREATE INDEX CONCURRENTLY tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c2,c3); +SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl_gist' ORDER BY indexname; +DROP TABLE tbl_gist; + + +/* + * 3. REINDEX + */ +CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box); +INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,10) AS x; +CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c3); +SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl_gist' ORDER BY indexname; +REINDEX INDEX tbl_gist_idx; +SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl_gist' ORDER BY indexname; +ALTER TABLE tbl_gist DROP COLUMN c1; +SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl_gist' ORDER BY indexname; +DROP TABLE tbl_gist; + +/* + * 4. Update, delete values in indexed table. + */ +CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box); +INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,10) AS x; +CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c3); +UPDATE tbl_gist SET c1 = 100 WHERE c1 = 2; +UPDATE tbl_gist SET c1 = 1 WHERE c1 = 3; +DELETE FROM tbl_gist WHERE c1 = 5 OR c3 = 12; +DROP TABLE tbl_gist; + +/* + * 5. Alter column type. + */ +CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box); +INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,10) AS x; +CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c3); +ALTER TABLE tbl_gist ALTER c1 TYPE bigint; +ALTER TABLE tbl_gist ALTER c3 TYPE bigint; +\d tbl_gist +DROP TABLE tbl_gist; + +/* + * 6. EXCLUDE constraint. + */ +CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box, EXCLUDE USING gist (c4 WITH &&) INCLUDE (c1, c2, c3)); +INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,10) AS x; +INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(3*x,2*x),point(3*x+1,2*x+1)) FROM generate_series(1,10) AS x; +EXPLAIN (costs off) SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10)); +\d tbl_gist +DROP TABLE tbl_gist; -- 2.11.0 --------------509C046B61362466E7741578-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 19 +++++++++++++++++++ src/include/catalog/pg_proc.dat | 4 ++++ src/test/regress/expected/arrays.out | 19 +++++++++++++++++++ src/test/regress/sql/arrays.sql | 16 ++++++++++++++++ 4 files changed, 58 insertions(+) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 1ab31a9056..c3e157622f 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17916,6 +17916,25 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array to the elements 1 through <parameter>n</parameter>. Usually these are + the first <parameter>n</parameter> elements of the array, but may not be if the lower + bound is different from 1. + </para> + <para> + <literal>select trim_array(ARRAY[1,2,3,4,5,6], 3)</literal> + <returnvalue>{1,2,3}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..0aae4daf3b 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,10 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', descr => 'trim an array down to n elements', + proname => 'trim_array', prolang => 'sql', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'select ($1)[1:$2]' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..a79ad36cb0 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,22 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('{1,2,3,4,5,6}'), + ('[-15:-10]={1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 3) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------ + [-15:-10]={1,2,3,4,5,6} | {} + {1,2,3,4,5,6} | {1,2,3} + [10:15]={1,2,3,4,5,6} | {} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30}} +(4 rows) + +DROP TABLE trim_array_test; diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..6d34cc468e 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,19 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('{1,2,3,4,5,6}'), + ('[-15:-10]={1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 3) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; -- 2.25.1 --------------5C31E1109E6FF9DF4672B1B7-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 17 +++++++++++++++++ src/include/catalog/pg_proc.dat | 4 ++++ src/test/regress/expected/arrays.out | 19 +++++++++++++++++++ src/test/regress/sql/arrays.sql | 16 ++++++++++++++++ 4 files changed, 56 insertions(+) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 1ab31a9056..58ab7860f4 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17916,6 +17916,23 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array to the first <parameter>n</parameter> elements. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 3)</literal> + <returnvalue>{1,2,3}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..76e2030865 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,10 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', descr => 'trim an array down to n elements', + proname => 'trim_array', prolang => 'sql', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'select ($1)[:array_lower($1, 1) + $2 - 1]' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..3fb1b8dcef 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,22 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('{1,2,3,4,5,6}'), + ('[-15:-10]={1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 3) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------ + [-15:-10]={1,2,3,4,5,6} | {1,2,3} + {1,2,3,4,5,6} | {1,2,3} + [10:15]={1,2,3,4,5,6} | {1,2,3} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30}} +(4 rows) + +DROP TABLE trim_array_test; diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..6d34cc468e 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,19 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('{1,2,3,4,5,6}'), + ('[-15:-10]={1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 3) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; -- 2.25.1 --------------FD71656EDFD96EADB800280D-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 1ab31a9056..aa5026af07 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17916,6 +17916,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index a24387c1e7..b9863f04e9 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------A2B3F8F0E95C0C23ACC1AE0D-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH] implement trim_array @ 2021-02-16 17:38 Vik Fearing <[email protected]> 0 siblings, 0 replies; 28+ messages in thread From: Vik Fearing @ 2021-02-16 17:38 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 18 ++++++++++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/utils/adt/arrayfuncs.c | 42 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 5 ++++ src/test/regress/expected/arrays.out | 23 +++++++++++++++ src/test/regress/sql/arrays.sql | 19 +++++++++++++ 6 files changed, 108 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 08f08322ca..2aac87cf8d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17909,6 +17909,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>trim_array</primary> + </indexterm> + <function>trim_array</function> ( <parameter>array</parameter> <type>anyarray</type>, <parameter>n</parameter> <type>integer</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Trims an array by removing the last <parameter>n</parameter> elements. + If the array is multidimensional, only the first dimension is trimmed. + </para> + <para> + <literal>trim_array(ARRAY[1,2,3,4,5,6], 2)</literal> + <returnvalue>{1,2,3,4}</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index ab0895ce3c..32eed988ab 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -398,7 +398,7 @@ S301 Enhanced UNNEST YES S401 Distinct types based on array types NO S402 Distinct types based on distinct types NO S403 ARRAY_MAX_CARDINALITY NO -S404 TRIM_ARRAY NO +S404 TRIM_ARRAY YES T011 Timestamp in Information Schema NO T021 BINARY and VARBINARY data types NO T022 Advanced support for BINARY and VARBINARY data types NO diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index f7012cc5d9..d38a99f0b0 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -6631,3 +6631,45 @@ width_bucket_array_variable(Datum operand, return left; } + +/* + * Trim the right N elements from an array by calculating an appropriate slice. + * Only the first dimension is trimmed. + */ +Datum +trim_array(PG_FUNCTION_ARGS) +{ + ArrayType *v = PG_GETARG_ARRAYTYPE_P(0); + int n = PG_GETARG_INT32(1); + int array_length = ARR_DIMS(v)[0]; + int16 elmlen; + bool elmbyval; + char elmalign; + int lower[MAXDIM]; + int upper[MAXDIM]; + bool lowerProvided[MAXDIM]; + bool upperProvided[MAXDIM]; + Datum result; + + /* Throw an error if out of bounds */ + if (n < 0 || n > array_length) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_ELEMENT_ERROR), + errmsg("number of elements to trim must be between 0 and %d", array_length))); + + /* Set all the bounds as unprovided except the first upper bound */ + memset(lowerProvided, 0, sizeof(lowerProvided)); + memset(upperProvided, 0, sizeof(upperProvided)); + upper[0] = ARR_LBOUND(v)[0] + array_length - n - 1; + upperProvided[0] = true; + + /* Fetch the needed information about the element type */ + get_typlenbyvalalign(ARR_ELEMTYPE(v), &elmlen, &elmbyval, &elmalign); + + /* Get the slice */ + result = array_get_slice(PointerGetDatum(v), 1, + upper, lower, upperProvided, lowerProvided, + -1, elmlen, elmbyval, elmalign); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1487710d59..8ab911238d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1674,6 +1674,11 @@ proname => 'arraycontjoinsel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int2 internal', prosrc => 'arraycontjoinsel' }, +{ oid => '8819', + descr => 'trim an array down to n elements', + proname => 'trim_array', proisstrict => 't', provolatile => 'i', + prorettype => 'anyarray', proargtypes => 'anyarray int4', + prosrc => 'trim_array' }, { oid => '764', descr => 'large object import', proname => 'lo_import', provolatile => 'v', proparallel => 'u', diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8bc7721e7d..fd3e4bfc49 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -2399,3 +2399,26 @@ SELECT width_bucket(5, ARRAY[3, 4, NULL]); ERROR: thresholds array must not contain NULLs SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); ERROR: thresholds must be one-dimensional array +-- trim_array +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + arr | trim_array +---------------------------------------------+------------------------------- + [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} + {1,2,3,4,5,6} | {1,2,3,4} + [10:15]={1,2,3,4,5,6} | {1,2,3,4} + {{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}} | {{1,10},{2,20},{3,30},{4,40}} +(4 rows) + +DROP TABLE trim_array_test; +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +ERROR: number of elements to trim must be between 0 and 3 +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail +ERROR: number of elements to trim must be between 0 and 3 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index c40619a8d5..551cf5c5c9 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -722,3 +722,22 @@ SELECT width_bucket(5, '{}'); SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); SELECT width_bucket(5, ARRAY[3, 4, NULL]); SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); + + +-- trim_array + +CREATE TABLE trim_array_test (arr integer[]); +INSERT INTO trim_array_test +VALUES ('[-15:-10]={1,2,3,4,5,6}'), + ('{1,2,3,4,5,6}'), + ('[10:15]={1,2,3,4,5,6}'), + ('{{1,10},{2,20},{3,30},{4,40},{5,50},{6,60}}'); + +SELECT arr, trim_array(arr, 2) +FROM trim_array_test +ORDER BY arr; + +DROP TABLE trim_array_test; + +VALUES (trim_array(ARRAY[1, 2, 3], -1)); -- fail +VALUES (trim_array(ARRAY[1, 2, 3], 10)); -- fail -- 2.25.1 --------------CD3F9594D6239C85433F5ED4-- ^ permalink raw reply [nested|flat] 28+ messages in thread
end of thread, other threads:[~2021-02-16 17:38 UTC | newest] Thread overview: 28+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-01-30 00:59 [PATCH] Covering GiST v10 Andreas Karlsson <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]> 2021-02-16 17:38 [PATCH] implement trim_array Vik Fearing <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox