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; +$$; +