Hi
When I wrote an reply to questing
https://stackoverflow.com/questions/66523737/postgresql-10-pl-pgsql-test-if-column-exits-in-a-record-variable
I found an interesting idea to have some basic functions and operators for
record type (similar to json, jsonb or hstore).
Now we can do almost all tasks on record type by cast to jsonb type. But
this transformation has some overhead (and for some tasks is not
necessary), and it is not too intuitive too.
I don't think so we need full functionality like hstore or jsonb (minimally
because record type cannot be persistent and indexed), but some basic
functionality can be useful.
-- tests of basic helper functions for record type
do $$
declare
r record;
k text; v text; t text;
begin
select oid, relname, relnamespace, reltype from pg_class limit 1 into r;
if not r ? 'xxx' then
raise notice 'pg_class has not column xxx';
end if;
if r ? 'relname' then
raise notice 'pg_class has column relname';
end if;
foreach k in array record_keys_array(r)
loop
raise notice '% => %', k, r->>k;
end loop;
raise notice '---';
-- second (slower) variant
for k in select * from record_keys(r)
loop
raise notice '% => %', k, r->>k;
end loop;
raise notice '---';
-- complete unpacking
for k, v, t in select * from record_each_text(r)
loop
raise notice '% => %(%)', k, v, t;
end loop;
end;
$$;
What do you think about this proposal?
Comments, notes?
Regards
Pavel
diff --git a/src/backend/utils/adt/rowtypes.c b/src/backend/utils/adt/rowtypes.c
index 23787a6ae7..664ccc6c44 100644
--- a/src/backend/utils/adt/rowtypes.c
+++ b/src/backend/utils/adt/rowtypes.c
@@ -20,9 +20,11 @@
#include "access/htup_details.h"
#include "catalog/pg_type.h"
#include "common/hashfn.h"
+#include "executor/spi.h"
#include "funcapi.h"
#include "libpq/pqformat.h"
#include "miscadmin.h"
+#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/datum.h"
#include "utils/lsyscache.h"
@@ -67,6 +69,15 @@ typedef struct RecordCompareData
ColumnCompareData columns[FLEXIBLE_ARRAY_MEMBER];
} RecordCompareData;
+/*
+ * Structure used for list of keys
+ */
+typedef struct
+{
+ ArrayBuildState *astate;
+ Tuplestorestate *tupstore;
+ TupleDesc tupdesc;
+} RecordKeysOutputData;
/*
* record_in - input routine for any composite type.
@@ -2015,3 +2026,360 @@ hash_record_extended(PG_FUNCTION_ARGS)
PG_RETURN_UINT64(result);
}
+
+/*
+ * Few simple functions and operators for work with record type.
+ */
+
+/*
+ * Returns true, if record has a field on top level.
+ */
+Datum
+record_exists(PG_FUNCTION_ARGS)
+{
+ HeapTupleHeader rec = PG_GETARG_HEAPTUPLEHEADER(0);
+ char *fname = text_to_cstring(PG_GETARG_TEXT_PP(1));
+ Oid tupType;
+ int32 tupTypmod;
+ TupleDesc tupdesc;
+ bool result = false;
+ int i;
+
+ /* Extract type info from the tuple itself */
+ tupType = HeapTupleHeaderGetTypeId(rec);
+ tupTypmod = HeapTupleHeaderGetTypMod(rec);
+ tupdesc = lookup_rowtype_tupdesc(tupType, tupTypmod);
+
+ for (i = 0; i < tupdesc->natts; i++)
+ {
+ Form_pg_attribute att = TupleDescAttr(tupdesc, i);
+ char *attname;
+
+ if (att->attisdropped)
+ continue;
+
+ attname = NameStr(att->attname);
+
+ if (strcmp(fname, attname) == 0)
+ {
+ result = true;
+ break;
+ }
+ }
+
+ ReleaseTupleDesc(tupdesc);
+
+ PG_RETURN_BOOL(result);
+}
+
+/*
+ * Returns text value of record field. Raise an error when required
+ * key doesn't exists
+ */
+Datum
+record_field_text(PG_FUNCTION_ARGS)
+{
+ HeapTupleHeader rec = PG_GETARG_HEAPTUPLEHEADER(0);
+ char *fname = text_to_cstring(PG_GETARG_TEXT_P(1));
+ HeapTupleData tuple;
+ Oid tupType;
+ int32 tupTypmod;
+ TupleDesc tupdesc;
+ int fno;
+ bool isnull = true;
+ char *outstr = NULL;
+
+ /* Extract type info from the tuple itself */
+ tupType = HeapTupleHeaderGetTypeId(rec);
+ tupTypmod = HeapTupleHeaderGetTypMod(rec);
+ tupdesc = lookup_rowtype_tupdesc(tupType, tupTypmod);
+
+ /* Build a temporary HeapTuple control structure */
+ tuple.t_len = HeapTupleHeaderGetDatumLength(rec);
+ ItemPointerSetInvalid(&(tuple.t_self));
+ tuple.t_tableOid = InvalidOid;
+ tuple.t_data = rec;
+
+ fno = SPI_fnumber(tupdesc, fname);
+ if (fno != SPI_ERROR_NOATTRIBUTE)
+ {
+ Datum value = SPI_getbinval(&tuple, tupdesc, fno, &isnull);
+
+ if (!isnull)
+ {
+ bool typIsVarlena;
+ Oid typoutput;
+ FmgrInfo proc;
+ Oid typeid;
+
+ typeid = SPI_gettypeid(tupdesc, fno);
+ getTypeOutputInfo(typeid, &typoutput, &typIsVarlena);
+ fmgr_info(typoutput, &proc);
+ outstr = OutputFunctionCall(&proc, value);
+ }
+ }
+
+ ReleaseTupleDesc(tupdesc);
+
+ if (!isnull)
+ {
+ Assert(outstr);
+
+ PG_RETURN_TEXT_P(cstring_to_text(outstr));
+ }
+
+ PG_RETURN_NULL();
+}
+
+/*
+ * Returns set of composite type (key text, value text, type text) of all
+ * fields in record.
+ */
+Datum
+record_each_text(PG_FUNCTION_ARGS)
+{
+ HeapTupleHeader rec = PG_GETARG_HEAPTUPLEHEADER(0);
+ HeapTupleData tuple;
+ Oid tupType;
+ int32 tupTypmod;
+ TupleDesc tupdesc;
+ int natts;
+ Datum *values;
+ bool *nulls;
+ int i;
+
+ ReturnSetInfo *rsi = (ReturnSetInfo *) fcinfo->resultinfo;
+ Tuplestorestate *rstupstore;
+ HeapTuple rstuple;
+ TupleDesc rstupdesc;
+ Datum rsvalues[3];
+ bool rsnulls[3] = {false, false, false};
+
+ MemoryContext old_cxt;
+
+ /* Extract type info from the tuple itself */
+ tupType = HeapTupleHeaderGetTypeId(rec);
+ tupTypmod = HeapTupleHeaderGetTypMod(rec);
+ tupdesc = lookup_rowtype_tupdesc(tupType, tupTypmod);
+ natts = tupdesc->natts;
+
+ /* check to see if caller supports us returning a tuplestore */
+ if (rsi == NULL || !IsA(rsi, ReturnSetInfo))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("set-valued function called in context that cannot accept a set")));
+ if (!(rsi->allowedModes & SFRM_Materialize))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("materialize mode required, but it is not allowed in this context")));
+
+ if (!rsi || !IsA(rsi, ReturnSetInfo) ||
+ (rsi->allowedModes & SFRM_Materialize) == 0 ||
+ rsi->expectedDesc == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("set-valued function called in context that "
+ "cannot accept a set")));
+
+ /* OK, prepare tuplestore in per-query memory */
+ old_cxt = MemoryContextSwitchTo(rsi->econtext->ecxt_per_query_memory);
+
+ rstupdesc = CreateTupleDescCopy(rsi->expectedDesc);
+ rstupstore = tuplestore_begin_heap(true, false, work_mem);
+
+ MemoryContextSwitchTo(old_cxt);
+
+ /* Build a temporary HeapTuple control structure */
+ tuple.t_len = HeapTupleHeaderGetDatumLength(rec);
+ ItemPointerSetInvalid(&(tuple.t_self));
+ tuple.t_tableOid = InvalidOid;
+ tuple.t_data = rec;
+
+ values = (Datum *) palloc(natts * sizeof(Datum));
+ nulls = (bool *) palloc(natts * sizeof(bool));
+
+ /* Break down the tuple into fields */
+ heap_deform_tuple(&tuple, tupdesc, values, nulls);
+
+ for (i = 0; i < natts; i++)
+ {
+ Form_pg_attribute att = TupleDescAttr(tupdesc, i);
+ Oid typ = att->atttypid;
+ int32 typmod = att->atttypmod;
+
+ /* Ignore dropped columns */
+ if (att->attisdropped)
+ continue;
+
+ rsvalues[0] = CStringGetTextDatum(NameStr(att->attname));
+ rsvalues[2] = CStringGetTextDatum(format_type_with_typemod(typ, typmod));
+
+ if (!nulls[i])
+ {
+ char *outstr;
+ bool typIsVarlena;
+ Oid typoutput;
+ FmgrInfo proc;
+
+ getTypeOutputInfo(typ, &typoutput, &typIsVarlena);
+ fmgr_info_cxt(typoutput, &proc, old_cxt);
+ outstr = OutputFunctionCall(&proc, values[i]);
+
+ rsvalues[1] = CStringGetTextDatum(outstr);
+ rsnulls[1] = false;
+ }
+ else
+ rsnulls[1] = true;
+
+ rstuple = heap_form_tuple(rstupdesc, rsvalues, rsnulls);
+
+ tuplestore_puttuple(rstupstore, rstuple);
+ }
+
+ ReleaseTupleDesc(tupdesc);
+
+ /* clean up and return the tuplestore */
+ tuplestore_donestoring(rstupstore);
+
+ rsi->returnMode = SFRM_Materialize;
+ rsi->setResult = rstupstore;
+ rsi->setDesc = rstupdesc;
+
+ pfree(values);
+ pfree(nulls);
+
+ return (Datum) 0;
+}
+
+/*
+ * Add field name to result (table or array)
+ */
+static void
+record_keys_accum_result(RecordKeysOutputData *rkstate, text *fname_value)
+{
+ if (rkstate->tupstore)
+ {
+ Datum values[1];
+ bool nulls[1];
+
+ values[0] = PointerGetDatum(fname_value);
+ nulls[0] = false;
+
+ tuplestore_putvalues(rkstate->tupstore,
+ rkstate->tupdesc,
+ values,
+ nulls);
+ }
+ else
+ {
+ rkstate->astate = accumArrayResult(rkstate->astate,
+ PointerGetDatum(fname_value),
+ false,
+ TEXTOID,
+ CurrentMemoryContext);
+ }
+}
+
+/*
+ * Prepares result of record_keys and record_keys_array functions.
+ * rkstate should be initialized before.
+ */
+static void
+record_keys_internal(FunctionCallInfo fcinfo, RecordKeysOutputData *rkstate)
+{
+ HeapTupleHeader rec = PG_GETARG_HEAPTUPLEHEADER(0);
+ Oid tupType;
+ int32 tupTypmod;
+ TupleDesc tupdesc;
+ int i;
+
+ /* Extract type info from the tuple itself */
+ tupType = HeapTupleHeaderGetTypeId(rec);
+ tupTypmod = HeapTupleHeaderGetTypMod(rec);
+ tupdesc = lookup_rowtype_tupdesc(tupType, tupTypmod);
+
+ for (i = 0; i < tupdesc->natts; i++)
+ {
+ Form_pg_attribute att = TupleDescAttr(tupdesc, i);
+ char *attname;
+
+ if (att->attisdropped)
+ continue;
+
+ attname = NameStr(att->attname);
+
+ record_keys_accum_result(rkstate, cstring_to_text(attname));
+ }
+
+ ReleaseTupleDesc(tupdesc);
+}
+
+/*
+ * Returns an array of keys of record on top level
+ */
+Datum
+record_keys(PG_FUNCTION_ARGS)
+{
+ RecordKeysOutputData rkstate;
+ ReturnSetInfo *rsi;
+ MemoryContext old_cxt;
+
+ rsi = (ReturnSetInfo *) fcinfo->resultinfo;
+
+ /* check to see if caller supports us returning a tuplestore */
+ if (rsi == NULL || !IsA(rsi, ReturnSetInfo))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("set-valued function called in context that cannot accept a set")));
+ if (!(rsi->allowedModes & SFRM_Materialize))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("materialize mode required, but it is not allowed in this context")));
+
+ if (!rsi || !IsA(rsi, ReturnSetInfo) ||
+ (rsi->allowedModes & SFRM_Materialize) == 0 ||
+ rsi->expectedDesc == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("set-valued function called in context that "
+ "cannot accept a set")));
+
+ /* OK, prepare tuplestore in per-query memory */
+ old_cxt = MemoryContextSwitchTo(rsi->econtext->ecxt_per_query_memory);
+
+ rkstate.astate = NULL;
+ rkstate.tupdesc = CreateTupleDescCopy(rsi->expectedDesc);
+ rkstate.tupstore = tuplestore_begin_heap(true, false, work_mem);
+
+ MemoryContextSwitchTo(old_cxt);
+
+ record_keys_internal(fcinfo, &rkstate);
+
+ tuplestore_donestoring(rkstate.tupstore);
+
+ rsi->returnMode = SFRM_Materialize;
+ rsi->setResult = rkstate.tupstore;
+ rsi->setDesc = rkstate.tupdesc;
+
+ return (Datum) 0;
+}
+
+/*
+ * Returns an array of keys of record on top level
+ */
+Datum
+record_keys_array(PG_FUNCTION_ARGS)
+{
+ RecordKeysOutputData rkstate;
+
+ /* For array output, rkstate should start as all zeroes */
+ memset(&rkstate, 0, sizeof(rkstate));
+
+ record_keys_internal(fcinfo, &rkstate);
+
+ if (rkstate.astate == NULL)
+ PG_RETURN_ARRAYTYPE_P(construct_empty_array(TEXTOID));
+
+ PG_RETURN_ARRAYTYPE_P(makeArrayResult(rkstate.astate,
+ CurrentMemoryContext));
+}
diff --git a/src/include/catalog/pg_operator.dat b/src/include/catalog/pg_operator.dat
index 85395a81ee..b74e05eb21 100644
--- a/src/include/catalog/pg_operator.dat
+++ b/src/include/catalog/pg_operator.dat
@@ -3473,4 +3473,10 @@
oprcode => 'multirange_after_multirange', oprrest => 'multirangesel',
oprjoin => 'scalargtjoinsel' },
+{ oid => '9335', descr => 'key exists',
+ oprname => '?', oprleft => 'record', oprright => 'text', oprresult => 'bool',
+ oprcode => 'record_exists' },
+{ oid => '9336', descr => 'get record field as text',
+ oprname => '->>', oprleft => 'record', oprright => 'text', oprresult => 'text',
+ oprcode => 'record_field_text' },
]
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 506689d8ac..72f8965587 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -11402,4 +11402,27 @@
proname => 'is_normalized', prorettype => 'bool', proargtypes => 'text text',
prosrc => 'unicode_is_normalized' },
+# utility functions for record type
+{ oid => '9330',
+ proname => 'record_exists', prorettype => 'bool',
+ proargtypes => 'record text', prosrc => 'record_exists' },
+
+{ oid => '9331',
+ proname => 'record_field_text', prorettype => 'text',
+ proargtypes => 'record text', prosrc => 'record_field_text' },
+
+{ oid => '9332', descr => 'key value pairs of a record',
+ proname => 'record_each_text', prorows => '100', proretset => 't',
+ prorettype => 'record', proargtypes => 'record',
+ proallargtypes => '{record,text,text,text}', proargmodes => '{i,o,o,o}',
+ proargnames => '{from_record,key,value,type}', prosrc => 'record_each_text' },
+
+{ oid => '9333', descr => 'returns keys of record on top level',
+ proname => 'record_keys', prorows => '100', proretset => 't',
+ prorettype => 'text', proargtypes => 'record', prosrc => 'record_keys' },
+
+{ oid => '9334', descr => 'returns an array of keys of record on top level',
+ proname => 'record_keys_array', prorettype => '_text',
+ proargtypes => 'record', prosrc => 'record_keys_array' }
+
]
diff --git a/src/pl/plpgsql/src/expected/plpgsql_record.out b/src/pl/plpgsql/src/expected/plpgsql_record.out
index 86d0665924..baa7f1ee47 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_record.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_record.out
@@ -791,3 +791,56 @@ table two_int8s_tab;
(42,42)
(1 row)
+-- tests of basic helper functions for record type
+do $$
+declare
+ r record;
+ k text; v text; t text;
+begin
+ select oid, relname, relnamespace, reltype from pg_class limit 1 into r;
+ if not r ? 'xxx' then
+ raise notice 'pg_class has not column xxx';
+ end if;
+
+ if r ? 'relname' then
+ raise notice 'pg_class has column relname';
+ end if;
+
+ foreach k in array record_keys_array(r)
+ loop
+ raise notice '% => %', k, r->>k;
+ end loop;
+
+ raise notice '---';
+
+ -- second (slower) variant
+ for k in select * from record_keys(r)
+ loop
+ raise notice '% => %', k, r->>k;
+ end loop;
+
+ raise notice '---';
+
+ -- complete unpacking
+ for k, v, t in select * from record_each_text(r)
+ loop
+ raise notice '% => %(%)', k, v, t;
+ end loop;
+end;
+$$;
+NOTICE: pg_class has not column xxx
+NOTICE: pg_class has column relname
+NOTICE: oid => 16385
+NOTICE: relname => complex
+NOTICE: relnamespace => 2200
+NOTICE: reltype => 16387
+NOTICE: ---
+NOTICE: oid => 16385
+NOTICE: relname => complex
+NOTICE: relnamespace => 2200
+NOTICE: reltype => 16387
+NOTICE: ---
+NOTICE: oid => 16385(oid)
+NOTICE: relname => complex(name)
+NOTICE: relnamespace => 2200(oid)
+NOTICE: reltype => 16387(oid)
diff --git a/src/pl/plpgsql/src/sql/plpgsql_record.sql b/src/pl/plpgsql/src/sql/plpgsql_record.sql
index 722048c730..634558ae76 100644
--- a/src/pl/plpgsql/src/sql/plpgsql_record.sql
+++ b/src/pl/plpgsql/src/sql/plpgsql_record.sql
@@ -518,3 +518,42 @@ insert into two_int8s_tab values (compresult(42));
-- reconnect so we lose any local knowledge of anonymous record types
\c -
table two_int8s_tab;
+
+-- tests of basic helper functions for record type
+do $$
+declare
+ r record;
+ k text; v text; t text;
+begin
+ select oid, relname, relnamespace, reltype from pg_class limit 1 into r;
+ if not r ? 'xxx' then
+ raise notice 'pg_class has not column xxx';
+ end if;
+
+ if r ? 'relname' then
+ raise notice 'pg_class has column relname';
+ end if;
+
+ foreach k in array record_keys_array(r)
+ loop
+ raise notice '% => %', k, r->>k;
+ end loop;
+
+ raise notice '---';
+
+ -- second (slower) variant
+ for k in select * from record_keys(r)
+ loop
+ raise notice '% => %', k, r->>k;
+ end loop;
+
+ raise notice '---';
+
+ -- complete unpacking
+ for k, v, t in select * from record_each_text(r)
+ loop
+ raise notice '% => %(%)', k, v, t;
+ end loop;
+end;
+$$;
+
