Hi all,

I needed introspection capabilities for record types to write more generic
trigger procedures in PL/PGSQL.

With the following patch it's possible to
- extract all field names of a record into an array
- extract field count of a record
- address a single field of a record with a variable
containing the field name (additional to the usual record.fieldname
notation where the fieldname is hardcoded).

The syntax is
- record%NFIELDS gives the number of fields in the record
- record%FIELDNAMES gives the array of the field names
- record%scalarvariable extracts the field whose name
is equal to the contents of scalarvariable

------------

The patch is nonintrusive in the sense that it only adds things
with one exception:
In function exec_eval_datum(), file pl_exec.c, line 3557 (after the patch)
I chose to convert the record field values to TEXT if the caller does
not require a certain type (expectedtypeid == InvalidOid).
Additionally, I cast the value from the record field type if
the destination Datum is of different type.

As far as I can see, this does no harm because in most cases
the expectedtypeid is correctly set. I just wanted to avoid that
if it is not set, the returned datum is of a more restrictive type
than TEXT.

------------

The patch is against a HEAD checkout from 07/12/05
The output comes from difforig.

Test code for the patch can be extracted from an example I put into
plpgsql.sgml

------------

Here is a summary of things that get patched by the file:
- add three new special parsing functions to pl_comp.c
(plpgsql_parse_wordpercentword, plpgsql_parse_wordnfields,
plpgsql_parse_wordfieldnames).
- modify PLpgSQL_recfield in plpgsql.h to either hold
a conventional field name (record.fieldname) or a dno
for the variable (record%variable).
- add two PLPGSQL_DTYPEs for the two new % notations
- modify "case PLPGSQL_DTYPE_RECFIELD:"
in exec_eval_datum() and exec_assign_value()
to deal with index strings from a variable
- add "case PLPGSQL_DTYPE_RECFIELDNAMES"
and "case PLPGSQL_DTYPE_NRECFIELD"
to exec_eval_datum() to evaluate %FIELDNAMES
and %NFIELDS expressions.
- update plpgsql.sgml in the docs directory

------------

Please notify me if I can be of further assistance.

Regards
Titus

*** ./doc/src/sgml/plpgsql.sgml.orig Thu Jul 14 00:49:33 2005
--- ./doc/src/sgml/plpgsql.sgml Thu Jul 14 01:15:41 2005
***************
*** 867,872 ****
--- 867,941 ----
</para>

<para>
+ To obtain the values of the fields the record is made up of,
+ the record variable can be qualified with the column or field
+ name. This can be done either literally with the usual dot notation
+ or the column name for indexing the record can be taken out of a scalar
+ variable. The syntax for this notation is Record_variable%IndexVariable.
+ To get information about the column fields, two special
+ notations exist that return all column names as an array (RecordVariable%FIELDNAMES)
+ and the count of fields stored in a record (RecordVariable%NFIELDS).
+ Thus, the RECORD can be viewed
+ as an associative array that allows for introspection of it's contents.
+ This feature is especially useful for writing generic triggers that
+ operate on records with unknown structure.
+ Here is an example that operates on the predefined record NEW in
+ a trigger procedure:
+ <programlisting>
+ CREATE OR REPLACE FUNCTION show_associative_records() RETURNS TRIGGER AS $ztt_u$
+ DECLARE
+ wmsg TEXT;
+ column TEXT;
+ colcontent TEXT;
+ colnames TEXT[];
+ coln INT4;
+ coli INT4;
+ testint8 INT8;
+ BEGIN
+ -- get the number of fields
+ coln := NEW%NFIELDS;
+ RAISE NOTICE 'Record type has % fields', coln;
+
+ -- obtain an array with all field names of the record
+ colnames := NEW%FIELDNAMES;
+ RAISE NOTICE 'All column names of test record: %', colnames;
+
+ -- show field names and contents of record
+ coli := 1;
+ LOOP
+ column := colnames[coli];
+ colcontent := NEW%column;
+ IF colcontent IS NULL
+ THEN
+ colcontent := '<NULL>';
+ END IF;
+ RAISE NOTICE 'column name % of NEW: value %', column, colcontent;
+ coli := coli + 1;
+ EXIT WHEN coli > coln;
+ END LOOP;
+
+ -- get a single record field value indexed by the contents of the variable "column"
+ -- raises an error if the record does not have the field name from "column"
+ column := 'SomeFieldNameInYourRecord';
+ -- testint8 will contain NULL if the value of this column
+ -- has a different type and cannot be casted to int8!
+ -- use a TEXT scalar variable to avoid this problem.
+ testint8 := NEW%column;
+ RAISE WARNING 'Column name = %, Column content = %', column, testint8;
+
+ -- the "normal" way with fixed field name with dot notation:
+ wmsg := NEW.SomeFieldNameInYourRecord;
+ RAISE NOTICE 'Column content NEW.SomeFieldNameInYourRecord = %', wmsg;
+
+ RETURN NULL;
+ END;
+ $ztt_u$ LANGUAGE plpgsql;
+

+
+ </programlisting>
+ </para>
+
+ <para>
Note that <literal>RECORD</> is not a true data type, only a placeholder.
One should also realize that when a <application>PL/pgSQL</application>
function is declared to return type <type>record</>, this is not quite the
*** ./src/pl/plpgsql/src/pl_comp.c.orig Wed Jul 13 19:22:43 2005
--- ./src/pl/plpgsql/src/pl_comp.c Thu Jul 14 00:25:42 2005
***************
*** 995,1001 ****

new = palloc(sizeof(PLpgSQL_recfield));
new->dtype = PLPGSQL_DTYPE_RECFIELD;
! new->fieldname = pstrdup(cp[1]);
new->recparentno = ns->itemno;

plpgsql_adddatum((PLpgSQL_datum *) new);
--- 995,1002 ----

new = palloc(sizeof(PLpgSQL_recfield));
new->dtype = PLPGSQL_DTYPE_RECFIELD;
! new->fieldindex.fieldname = strdup(cp[1]);
! new->fieldindex_flag = RECFIELD_USE_FIELDNAME;
new->recparentno = ns->itemno;

plpgsql_adddatum((PLpgSQL_datum *) new);
***************
*** 1101,1107 ****

new = palloc(sizeof(PLpgSQL_recfield));
new->dtype = PLPGSQL_DTYPE_RECFIELD;
! new->fieldname = pstrdup(cp[2]);
new->recparentno = ns->itemno;

plpgsql_adddatum((PLpgSQL_datum *) new);
--- 1102,1109 ----

new = palloc(sizeof(PLpgSQL_recfield));
new->dtype = PLPGSQL_DTYPE_RECFIELD;
! new->fieldindex.fieldname = strdup(cp[2]);
! new->fieldindex_flag = RECFIELD_USE_FIELDNAME;
new->recparentno = ns->itemno;

plpgsql_adddatum((PLpgSQL_datum *) new);
***************
*** 1551,1556 ****
--- 1553,1736 ----
return T_DTYPE;
}

+ /* ----------
+ * plpgsql_parse_wordpercentword
+ * lookup associative index into record
+ * ----------
+ */
+ int
+ plpgsql_parse_wordpercentword(char *word)
+ {
+ PLpgSQL_nsitem *ns1, *ns2;
+ char *cp[2];
+ int ret = T_ERROR;
+
+ /* convert % to . for plpgsql_convert_ident */
+ char * percl = strchr(word, '%');
+ if ( percl == NULL )
+ return T_ERROR;
+ *percl = '.';
+
+ /* Do case conversion and word separation */
+ plpgsql_convert_ident(word, cp, 2);
+ *percl = '%';
+
+ /*
+ * Lookup the first word
+ */
+ ns1 = plpgsql_ns_lookup(cp[0], NULL);
+ if ( ns1 == NULL )
+ {
+ pfree(cp[0]);
+ pfree(cp[1]);
+ return T_ERROR;
+ }
+
+ ns2 = plpgsql_ns_lookup(cp[1], NULL);
+ pfree(cp[0]);
+ pfree(cp[1]);
+ if ( ns2 == NULL ) /* name lookup failed */
+ return T_ERROR;
+
+ switch (ns1->itemtype)
+ {
+ case PLPGSQL_NSTYPE_REC:
+ {
+ /*
+ * First word is a record name, so second word must be an
+ * variable holding the field name in this record.
+ */
+ if ( ns2->itemtype == PLPGSQL_NSTYPE_VAR ) {
+ PLpgSQL_recfield *new;
+
+ new = malloc(sizeof(PLpgSQL_recfield));
+ new->dtype = PLPGSQL_DTYPE_RECFIELD;
+ new->fieldindex.indexvar_no = ns2->itemno;
+ new->fieldindex_flag = RECFIELD_USE_INDEX_VAR;
+ new->recparentno = ns1->itemno;
+
+ plpgsql_adddatum((PLpgSQL_datum *) new);
+
+ plpgsql_yylval.scalar = (PLpgSQL_datum *) new;
+ ret = T_SCALAR;
+ } /* IF VAR */
+ break;
+ }
+ default:
+ break;
+ }
+ return ret;
+ } /* plpgsql_parse_wordpercentword */
+
+ /* ----------
+ * plpgsql_parse_wordnfields
+ * create # of fields in a record
+ * ----------
+ */
+ int
+ plpgsql_parse_wordnfields(char *word)
+ {
+ PLpgSQL_nsitem *ns1;
+ char *cp[2];
+ int ret = T_ERROR;
+
+ /* convert % to . for plpgsql_convert_ident */
+ int i = strlen(word) - 8;
+ Assert(word[i] == '%');
+ word[i] = '.';
+ /* Do case conversion and word separation */
+ plpgsql_convert_ident(word, cp, 2);
+ word[i] = '%';
+
+ /*
+ * Lookup the first word
+ */
+ ns1 = plpgsql_ns_lookup(cp[0], NULL);
+ if ( ns1 == NULL )
+ {
+ pfree(cp[0]);
+ pfree(cp[1]);
+ return T_ERROR;
+ }
+
+ pfree(cp[0]);
+ pfree(cp[1]);
+ switch (ns1->itemtype)
+ {
+ case PLPGSQL_NSTYPE_REC:
+ {
+ PLpgSQL_recfieldproperties *new;
+
+ new = malloc(sizeof(PLpgSQL_recfieldproperties));
+ new->dtype = PLPGSQL_DTYPE_NRECFIELD;
+ new->recparentno = ns1->itemno;
+ plpgsql_adddatum((PLpgSQL_datum *) new);
+ plpgsql_yylval.scalar = (PLpgSQL_datum *) new;
+ ret = T_SCALAR; /* ??? */
+ break;
+ }
+ default:
+ break;
+ }
+ return ret;
+ } /* plpgsql_parse_wordnfields */
+
+
+ /* ----------
+ * plpgsql_parse_wordfieldnames
+ * create fieldnames of a record
+ * ----------
+ */
+ int
+ plpgsql_parse_wordfieldnames(char *word)
+ {
+ PLpgSQL_nsitem *ns1;
+ char *cp[2];
+ int ret = T_ERROR;
+
+ /* convert % to . for plpgsql_convert_ident */
+ int i = strlen(word) - 11;
+ Assert(word[i] == '%');
+ word[i] = '.';
+ /* Do case conversion and word separation */
+ plpgsql_convert_ident(word, cp, 2);
+ word[i] = '%';
+
+ /*
+ * Lookup the first word
+ */
+ ns1 = plpgsql_ns_lookup(cp[0], NULL);
+ if ( ns1 == NULL )
+ {
+ pfree(cp[0]);
+ pfree(cp[1]);
+ return T_ERROR;
+ }
+
+ pfree(cp[0]);
+ pfree(cp[1]);
+
+ switch (ns1->itemtype)
+ {
+ case PLPGSQL_NSTYPE_REC:
+ {
+ PLpgSQL_recfieldproperties *new;
+
+ new = malloc(sizeof(PLpgSQL_recfieldproperties));
+ new->dtype = PLPGSQL_DTYPE_RECFIELDNAMES;
+ new->recparentno = ns1->itemno;
+ plpgsql_adddatum((PLpgSQL_datum *) new);
+ plpgsql_yylval.scalar = (PLpgSQL_datum *) new;
+ ret = T_SCALAR; /* ??? */
+ break;
+ }
+ default:
+ break;
+ }
+ return ret;
+ } /* plpgsql_parse_wordfieldnames */
+
+
/*
* plpgsql_build_variable - build a datum-array entry of a given
* datatype
*** ./src/pl/plpgsql/src/pl_exec.c.orig Wed Jul 13 19:22:08 2005
--- ./src/pl/plpgsql/src/pl_exec.c Thu Jul 14 00:42:51 2005
***************
*** 716,721 ****
--- 716,723 ----
case PLPGSQL_DTYPE_RECFIELD:
case PLPGSQL_DTYPE_ARRAYELEM:
case PLPGSQL_DTYPE_TRIGARG:
+ case PLPGSQL_DTYPE_NRECFIELD:
+ case PLPGSQL_DTYPE_RECFIELDNAMES:
/*
* These datum records are read-only at runtime, so no need
* to copy them
***************
*** 825,830 ****
--- 827,834 ----

case PLPGSQL_DTYPE_RECFIELD:
case PLPGSQL_DTYPE_ARRAYELEM:
+ case PLPGSQL_DTYPE_NRECFIELD:
+ case PLPGSQL_DTYPE_RECFIELDNAMES:
break;

default:
***************
*** 3154,3165 ****
* Get the number of the records field to change and the
* number of attributes in the tuple.
*/
! fno = SPI_fnumber(rec->tupdesc, recfield->fieldname);
! if (fno == SPI_ERROR_NOATTRIBUTE)
ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\" has no field \"%s\"",
! rec->refname, recfield->fieldname)));
fno--;
natts = rec->tupdesc->natts;

--- 3158,3192 ----
* Get the number of the records field to change and the
* number of attributes in the tuple.
*/
! if ( recfield->fieldindex_flag == RECFIELD_USE_FIELDNAME ) {
! fno = SPI_fnumber(rec->tupdesc, recfield->fieldindex.fieldname);
! if (fno == SPI_ERROR_NOATTRIBUTE)
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\" has no field \"%s\"",
! rec->refname, recfield->fieldindex.fieldname)));
! }
! else if ( recfield->fieldindex_flag == RECFIELD_USE_INDEX_VAR ) {
! PLpgSQL_var * idxvar = (PLpgSQL_var *) (estate->datums[recfield->fieldindex.indexvar_no]);
! char * fname = convert_value_to_string(idxvar->value, idxvar->datatype->typoid);
! if ( fname == NULL )
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\": cannot evaluate variable to record index string",
! rec->refname)));
! fno = SPI_fnumber(rec->tupdesc, fname);
! pfree(fname);
! if (fno == SPI_ERROR_NOATTRIBUTE)
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\" has no field \"%s\"",
! rec->refname, fname)));
! }
! else
ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\": internal error",
! rec->refname)));
fno--;
natts = rec->tupdesc->natts;

***************
*** 3497,3515 ****
errmsg("record \"%s\" is not assigned yet",
rec->refname),
errdetail("The tuple structure of a not-yet-assigned record is indeterminate.")));
! fno = SPI_fnumber(rec->tupdesc, recfield->fieldname);
! if (fno == SPI_ERROR_NOATTRIBUTE)
ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\" has no field \"%s\"",
! rec->refname, recfield->fieldname)));
! *typeid = SPI_gettypeid(rec->tupdesc, fno);
! *value = SPI_getbinval(rec->tup, rec->tupdesc, fno, isnull);
! if (expectedtypeid != InvalidOid && expectedtypeid != *typeid)
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("type of \"%s.%s\" does not match that when preparing the plan",
! rec->refname, recfield->fieldname)));
break;
}

--- 3524,3670 ----
errmsg("record \"%s\" is not assigned yet",
rec->refname),
errdetail("The tuple structure of a not-yet-assigned record is indeterminate.")));
! if ( recfield->fieldindex_flag == RECFIELD_USE_FIELDNAME ) {
! fno = SPI_fnumber(rec->tupdesc, recfield->fieldindex.fieldname);
! if (fno == SPI_ERROR_NOATTRIBUTE)
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\" has no field \"%s\"",
! rec->refname, recfield->fieldindex.fieldname)));
! }
! else if ( recfield->fieldindex_flag == RECFIELD_USE_INDEX_VAR ) {
! PLpgSQL_var * idxvar = (PLpgSQL_var *) (estate->datums[recfield->fieldindex.indexvar_no]);
! char * fname = convert_value_to_string(idxvar->value, idxvar->datatype->typoid);
! if ( fname == NULL )
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\": cannot evaluate variable to record index string",
! rec->refname)));
! fno = SPI_fnumber(rec->tupdesc, fname);
! pfree(fname);
! if (fno == SPI_ERROR_NOATTRIBUTE)
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\" has no field \"%s\"",
! rec->refname, fname)));
! }
! else
ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\": internal error",
! rec->refname)));
!
! /* Do not allow typeids to become "narrowed" by InvalidOids
! causing specialized typeids from the tuple restricting the destination */
! if ( expectedtypeid == InvalidOid )
! expectedtypeid = TEXTOID; /* TEXT should be able to cope with anything */
! /* Want to be able store the value in any compatible variable */
! if ( expectedtypeid != SPI_gettypeid(rec->tupdesc, fno) ) {
! Datum cval = SPI_getbinval(rec->tup, rec->tupdesc, fno, isnull);
! /* elog(WARNING, "casting record field value to expected value!"); */
! cval = exec_simple_cast_value(cval,
! SPI_gettypeid(rec->tupdesc, fno),
! expectedtypeid,
! -1,
! isnull);
!
! *value = cval;
! *typeid = expectedtypeid;
! /* ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("type of \"%s\" does not match that when preparing the plan",
! rec->refname)));
! */
! } /* IF expected typeid is valid but does not match! */
! else { /* expected typeid matches */
! *value = SPI_getbinval(rec->tup, rec->tupdesc, fno, isnull);
! *typeid = SPI_gettypeid(rec->tupdesc, fno);
! } /* ELSE */
! break;
! }
!
! case PLPGSQL_DTYPE_RECFIELDNAMES:
! /* Construct array datum from record field names */
! {
! Oid arraytypeid,
! arrayelemtypeid = TEXTOID;
! int16 arraytyplen,
! elemtyplen;
! bool elemtypbyval;
! char elemtypalign;
! ArrayType *arrayval;
!
! PLpgSQL_rec *rec = (PLpgSQL_rec *) (estate->datums[((PLpgSQL_recfieldproperties *) datum)->recparentno]);
! int fc, tfc = 0;
! Datum *arrayelems;
! char *fieldname;
!
! if (!HeapTupleIsValid(rec->tup))
! ereport(ERROR,
! (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE) ,
! errmsg("record \"%s\" is not assigned yet",
! rec->refname),
! errdetail("The tuple structure of a not-yet-assigned record is indeterminate.")));
! arrayelems = palloc(sizeof(Datum) * rec->tupdesc->natts);
! arraytypeid = get_array_type(arrayelemtypeid);
! arraytyplen = get_typlen(arraytypeid);
! get_typlenbyvalalign(arrayelemtypeid,
! &elemtyplen,
! &elemtypbyval,
! &elemtypalign);
!
! if ( expectedtypeid != InvalidOid && expectedtypeid != arraytypeid )
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("type of \"%s\" does not match array type when preparing the plan",
! rec->refname)));
! for ( fc = 0; fc < rec->tupdesc->natts; ++fc ) {
! fieldname = SPI_fname(rec->tupdesc, fc+1);
! if ( fieldname ) {
! arrayelems[fc] = DirectFunctionCall1(textin, CStringGetDatum(fieldname));
! pfree(fieldname);
! ++tfc;
! } /* IF */
! } /* FOR */
!
! arrayval = construct_array(arrayelems, tfc,
! arrayelemtypeid,
! elemtyplen,
! elemtypbyval,
! elemtypalign);
!
!
! /* construct_array copies data; free temp elem array */
! #if 0
! for ( fc = 0; fc < tfc; ++fc )
! pfree(DatumGetPointer(arrayelems[fc]);
! pfree(arrayelems);
! #endif
! *value = PointerGetDatum(arrayval);
! *typeid = arraytypeid;
! *isnull = false;
! break;
! }
!
! case PLPGSQL_DTYPE_NRECFIELD:
! /* Get # of fields in a record */
! {
! PLpgSQL_rec *rec = (PLpgSQL_rec *) (estate->datums[((PLpgSQL_recfieldproperties *) datum)->recparentno]);
!
! if (!HeapTupleIsValid(rec->tup))
! ereport(ERROR,
! (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE) ,
! errmsg("record \"%s\" is not assigned yet",
! rec->refname),
! errdetail("The tuple structure of a not-yet-assigned record is indeterminate.")));
! if ( expectedtypeid != InvalidOid && expectedtypeid != INT4OID )
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("type of \"%s\" does not match scalar type",
! rec->refname)));
! *value = Int32GetDatum(rec->tupdesc->natts);
! *typeid = INT4OID;
! *isnull = false;
break;
}

*** ./src/pl/plpgsql/src/pl_funcs.c.orig Wed Jul 13 19:23:08 2005
--- ./src/pl/plpgsql/src/pl_funcs.c Wed Jul 13 19:32:17 2005
***************
*** 1066,1074 ****
printf("REC %s\n", ((PLpgSQL_rec *) d)->refname);
break;
case PLPGSQL_DTYPE_RECFIELD:
! printf("RECFIELD %-16s of REC %d\n",
! ((PLpgSQL_recfield *) d)->fieldname,
! ((PLpgSQL_recfield *) d)->recparentno);
break;
case PLPGSQL_DTYPE_ARRAYELEM:
printf("ARRAYELEM of VAR %d subscript ",
--- 1066,1078 ----
printf("REC %s\n", ((PLpgSQL_rec *) d)->refname);
break;
case PLPGSQL_DTYPE_RECFIELD:
! if ( ((PLpgSQL_recfield *) d)->fieldindex_flag == RECFIELD_USE_FIELDNAME )
! printf("RECFIELD %-16s of REC %d\n",
! ((PLpgSQL_recfield *) d)->fieldindex.fieldname,
! ((PLpgSQL_recfield *) d)->recparentno);
! else
! printf("RECFIELD Variable of REC %d\n",
! ((PLpgSQL_recfield *) d)->recparentno);
break;
case PLPGSQL_DTYPE_ARRAYELEM:
printf("ARRAYELEM of VAR %d subscript ",
*** ./src/pl/plpgsql/src/plpgsql.h.orig Wed Jul 13 19:21:32 2005
--- ./src/pl/plpgsql/src/plpgsql.h Wed Jul 13 19:19:30 2005
***************
*** 73,79 ****
PLPGSQL_DTYPE_RECFIELD,
PLPGSQL_DTYPE_ARRAYELEM,
PLPGSQL_DTYPE_EXPR,
! PLPGSQL_DTYPE_TRIGARG
};

/* ----------
--- 73,81 ----
PLPGSQL_DTYPE_RECFIELD,
PLPGSQL_DTYPE_ARRAYELEM,
PLPGSQL_DTYPE_EXPR,
! PLPGSQL_DTYPE_TRIGARG,
! PLPGSQL_DTYPE_RECFIELDNAMES,
! PLPGSQL_DTYPE_NRECFIELD
};

/* ----------
***************
*** 269,278 ****
{ /* Field in record */
int dtype;
int rfno;
! char *fieldname;
int recparentno; /* dno of parent record */
} PLpgSQL_recfield;


typedef struct
{ /* Element of array variable */
--- 271,294 ----
{ /* Field in record */
int dtype;
int rfno;
! union {
! char *fieldname;
! int indexvar_no; /* dno of variable holding index string */
! } fieldindex;
! enum {
! RECFIELD_USE_FIELDNAME,
! RECFIELD_USE_INDEX_VAR,
! } fieldindex_flag;
int recparentno; /* dno of parent record */
} PLpgSQL_recfield;

+ typedef struct
+ { /* Field in record */
+ int dtype;
+ int rfno;
+ int recparentno; /* dno of parent record */
+ } PLpgSQL_recfieldproperties;
+

typedef struct
{ /* Element of array variable */
***************
*** 678,683 ****
--- 694,702 ----
extern int plpgsql_parse_tripwordtype(char *word);
extern int plpgsql_parse_wordrowtype(char *word);
extern int plpgsql_parse_dblwordrowtype(char *word);
+ extern int plpgsql_parse_wordnfields(char *word);
+ extern int plpgsql_parse_wordfieldnames(char *word);
+ extern int plpgsql_parse_wordpercentword(char *word);
extern PLpgSQL_type *plpgsql_parse_datatype(const char *string);
extern PLpgSQL_type *plpgsql_build_datatype(Oid typeOid, int32 typmod);
extern PLpgSQL_variable *plpgsql_build_variable(const char *refname, int lineno,
*** ./src/pl/plpgsql/src/scan.l.orig Wed Jul 13 19:21:59 2005
--- ./src/pl/plpgsql/src/scan.l Wed Jul 13 19:33:30 2005
***************
*** 243,248 ****
--- 243,257 ----
{param}{space}*\.{space}*{identifier}{space}*%ROWT YPE {
plpgsql_error_lineno = plpgsql_scanner_lineno();
return plpgsql_parse_dblwordrowtype(yytext); }
+ {identifier}{space}*%NFIELDS {
+ plpgsql_error_lineno = plpgsql_scanner_lineno();
+ return plpgsql_parse_wordnfields(yytext); }
+ {identifier}{space}*%FIELDNAMES {
+ plpgsql_error_lineno = plpgsql_scanner_lineno();
+ return plpgsql_parse_wordfieldnames(yytext); }
+ {identifier}{space}*%{identifier} {
+ plpgsql_error_lineno = plpgsql_scanner_lineno();
+ return plpgsql_parse_wordpercentword(yytext); }

{digit}+ { return T_NUMBER; }



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Titus von Boxberg wrote:
> With the following patch it's possible to
> - extract all field names of a record into an array
> - extract field count of a record
> - address a single field of a record with a variable
> containing the field name (additional to the usual record.fieldname
> notation where the fieldname is hardcoded).

I wonder if this is the right syntax. record%identifier is doing
something fundamentally different from record.identifier, but the syntax
doesn't make that clear. I don't have any concrete suggestions for
improvement, mind you...

> Test code for the patch can be extracted from an example I put into
> plpgsql.sgml

Can you supply some proper regression tests, please? i.e. patch
sql/plpgsql.sql and expected/plpgsql.out in src/test/regress

A few minor comments from skimming the patch:

> ***************
> *** 995,1001 ****
>
> new = palloc(sizeof(PLpgSQL_recfield));
> new->dtype = PLPGSQL_DTYPE_RECFIELD;
> ! new->fieldname = pstrdup(cp[1]);
> new->recparentno = ns->itemno;
>
> plpgsql_adddatum((PLpgSQL_datum *) new);
> --- 995,1002 ----
>
> new = palloc(sizeof(PLpgSQL_recfield));
> new->dtype = PLPGSQL_DTYPE_RECFIELD;
> ! new->fieldindex.fieldname = strdup(cp[1]);
> ! new->fieldindex_flag = RECFIELD_USE_FIELDNAME;
> new->recparentno = ns->itemno;
>
> plpgsql_adddatum((PLpgSQL_datum *) new);

Should be pstrdup().

> ***************
> *** 1101,1107 ****
>
> new = palloc(sizeof(PLpgSQL_recfield));
> new->dtype = PLPGSQL_DTYPE_RECFIELD;
> ! new->fieldname = pstrdup(cp[2]);
> new->recparentno = ns->itemno;
>
> plpgsql_adddatum((PLpgSQL_datum *) new);
> --- 1102,1109 ----
>
> new = palloc(sizeof(PLpgSQL_recfield));
> new->dtype = PLPGSQL_DTYPE_RECFIELD;
> ! new->fieldindex.fieldname = strdup(cp[2]);
> ! new->fieldindex_flag = RECFIELD_USE_FIELDNAME;
> new->recparentno = ns->itemno;
>
> plpgsql_adddatum((PLpgSQL_datum *) new);

Ibid.

> + switch (ns1->itemtype)
> + {
> + case PLPGSQL_NSTYPE_REC:
> + {
> + PLpgSQL_recfieldproperties *new;
> +
> + new = malloc(sizeof(PLpgSQL_recfieldproperties));
> + new->dtype = PLPGSQL_DTYPE_NRECFIELD;
> + new->recparentno = ns1->itemno;
> + plpgsql_adddatum((PLpgSQL_datum *) new);
> + plpgsql_yylval.scalar = (PLpgSQL_datum *) new;
> + ret = T_SCALAR; /* ??? */
> + break;

Should be palloc().

> + return ret;
> + } /* plpgsql_parse_wordnfields */

The Postgres convention is not to include comments like this.

> + case PLPGSQL_NSTYPE_REC:
> + {
> + PLpgSQL_recfieldproperties *new;
> +
> + new = malloc(sizeof(PLpgSQL_recfieldproperties));
> + new->dtype = PLPGSQL_DTYPE_RECFIELDNAMES;
> + new->recparentno = ns1->itemno;
> + plpgsql_adddatum((PLpgSQL_datum *) new);
> + plpgsql_yylval.scalar = (PLpgSQL_datum *) new;
> + ret = T_SCALAR; /* ??? */
> + break;
> + }

Should be palloc().

> ! /* construct_array copies data; free temp elem array */
> ! #if 0
> ! for ( fc = 0; fc < tfc; ++fc )
> ! pfree(DatumGetPointer(arrayelems[fc]);
> ! pfree(arrayelems);
> ! #endif

Unexplained #if 0 blocks aren't good.

-Neil

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

good idea. it's what can eliminate not neccessery using plperl. I would to
see it in plpgsql.

regards
Pavel

On Thu, 14 Jul 2005, Titus von Boxberg wrote:

> Hi all,
>
> I needed introspection capabilities for record types to write more generic
> trigger procedures in PL/PGSQL.
>
> With the following patch it's possible to
> - extract all field names of a record into an array
> - extract field count of a record
> - address a single field of a record with a variable
> containing the field name (additional to the usual record.fieldname
> notation where the fieldname is hardcoded).
>
> The syntax is
> - record%NFIELDS gives the number of fields in the record
> - record%FIELDNAMES gives the array of the field names
> - record%scalarvariable extracts the field whose name
> is equal to the contents of scalarvariable
>
> ------------
>
> The patch is nonintrusive in the sense that it only adds things
> with one exception:
> In function exec_eval_datum(), file pl_exec.c, line 3557 (after the patch)
> I chose to convert the record field values to TEXT if the caller does
> not require a certain type (expectedtypeid == InvalidOid).
> Additionally, I cast the value from the record field type if
> the destination Datum is of different type.
>
> As far as I can see, this does no harm because in most cases
> the expectedtypeid is correctly set. I just wanted to avoid that
> if it is not set, the returned datum is of a more restrictive type
> than TEXT.
>
> ------------
>
> The patch is against a HEAD checkout from 07/12/05
> The output comes from difforig.
>
> Test code for the patch can be extracted from an example I put into
> plpgsql.sgml
>
> ------------
>
> Here is a summary of things that get patched by the file:
> - add three new special parsing functions to pl_comp.c
> (plpgsql_parse_wordpercentword, plpgsql_parse_wordnfields,
> plpgsql_parse_wordfieldnames).
> - modify PLpgSQL_recfield in plpgsql.h to either hold
> a conventional field name (record.fieldname) or a dno
> for the variable (record%variable).
> - add two PLPGSQL_DTYPEs for the two new % notations
> - modify "case PLPGSQL_DTYPE_RECFIELD:"
> in exec_eval_datum() and exec_assign_value()
> to deal with index strings from a variable
> - add "case PLPGSQL_DTYPE_RECFIELDNAMES"
> and "case PLPGSQL_DTYPE_NRECFIELD"
> to exec_eval_datum() to evaluate %FIELDNAMES
> and %NFIELDS expressions.
> - update plpgsql.sgml in the docs directory
>
> ------------
>
> Please notify me if I can be of further assistance.
>
> Regards
> Titus
>


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Neil Conway schrieb:

> Titus von Boxberg wrote:
>
> Can you supply some proper regression tests, please? i.e. patch sql/plpgsql.sql and
expected/plpgsql.out in src/test/regress

In sql/plpgsql.sql I have added a function testing the new features
and altered expected/plpgsql.out

>
> A few minor comments from skimming the patch:

Done.

------------------

The modifications to the first one are the following:
- restored type evaluation in RECFIELD value evaluation
in exec_eval_datum to the previous semantics.
The addition should now be completely invisible to existing code.
- instead do a check in exec_eval_expr() if the expression
contains datums of the type record%variable. For these
the type cannot be stored in a plan because the type might change
between evaluations of this expr. --> free and reprepare the plan.
- storage allocated by exec_eval_expr() for the expression
record%FIELDNAMES (the array containing the names)
is freed in exec_eval_cleanup

------------------

regression tests are ok on my system.

Do you agree with the mods and the patch?

Regards
Titus

*** ./doc/src/sgml/plpgsql.sgml.orig Thu Jul 14 00:49:33 2005
--- ./doc/src/sgml/plpgsql.sgml Thu Jul 14 01:15:41 2005
***************
*** 867,872 ****
--- 867,941 ----
</para>

<para>
+ To obtain the values of the fields the record is made up of,
+ the record variable can be qualified with the column or field
+ name. This can be done either literally with the usual dot notation
+ or the column name for indexing the record can be taken out of a scalar
+ variable. The syntax for this notation is Record_variable%IndexVariable.
+ To get information about the column fields, two special
+ notations exist that return all column names as an array (RecordVariable%FIELDNAMES)
+ and the count of fields stored in a record (RecordVariable%NFIELDS).
+ Thus, the RECORD can be viewed
+ as an associative array that allows for introspection of it's contents.
+ This feature is especially useful for writing generic triggers that
+ operate on records with unknown structure.
+ Here is an example that operates on the predefined record NEW in
+ a trigger procedure:
+ <programlisting>
+ CREATE OR REPLACE FUNCTION show_associative_records() RETURNS TRIGGER AS $ztt_u$
+ DECLARE
+ wmsg TEXT;
+ column TEXT;
+ colcontent TEXT;
+ colnames TEXT[];
+ coln INT4;
+ coli INT4;
+ testint8 INT8;
+ BEGIN
+ -- get the number of fields
+ coln := NEW%NFIELDS;
+ RAISE NOTICE 'Record type has % fields', coln;
+
+ -- obtain an array with all field names of the record
+ colnames := NEW%FIELDNAMES;
+ RAISE NOTICE 'All column names of test record: %', colnames;
+
+ -- show field names and contents of record
+ coli := 1;
+ LOOP
+ column := colnames[coli];
+ colcontent := NEW%column;
+ IF colcontent IS NULL
+ THEN
+ colcontent := '<NULL>';
+ END IF;
+ RAISE NOTICE 'column name % of NEW: value %', column, colcontent;
+ coli := coli + 1;
+ EXIT WHEN coli > coln;
+ END LOOP;
+
+ -- get a single record field value indexed by the contents of the variable "column"
+ -- raises an error if the record does not have the field name from "column"
+ column := 'SomeFieldNameInYourRecord';
+ -- testint8 will contain NULL if the value of this column
+ -- has a different type and cannot be casted to int8!
+ -- use a TEXT scalar variable to avoid this problem.
+ testint8 := NEW%column;
+ RAISE WARNING 'Column name = %, Column content = %', column, testint8;
+
+ -- the "normal" way with fixed field name with dot notation:
+ wmsg := NEW.SomeFieldNameInYourRecord;
+ RAISE NOTICE 'Column content NEW.SomeFieldNameInYourRecord = %', wmsg;
+
+ RETURN NULL;
+ END;
+ $ztt_u$ LANGUAGE plpgsql;
+

+
+ </programlisting>
+ </para>
+
+ <para>
Note that <literal>RECORD</> is not a true data type, only a placeholder.
One should also realize that when a <application>PL/pgSQL</application>
function is declared to return type <type>record</>, this is not quite the
*** ./src/pl/plpgsql/src/pl_comp.c.orig Wed Jul 13 19:22:43 2005
--- ./src/pl/plpgsql/src/pl_comp.c Thu Jul 14 23:54:26 2005
***************
*** 995,1001 ****

new = palloc(sizeof(PLpgSQL_recfield));
new->dtype = PLPGSQL_DTYPE_RECFIELD;
! new->fieldname = pstrdup(cp[1]);
new->recparentno = ns->itemno;

plpgsql_adddatum((PLpgSQL_datum *) new);
--- 995,1002 ----

new = palloc(sizeof(PLpgSQL_recfield));
new->dtype = PLPGSQL_DTYPE_RECFIELD;
! new->fieldindex.fieldname = pstrdup(cp[1]);
! new->fieldindex_flag = RECFIELD_USE_FIELDNAME;
new->recparentno = ns->itemno;

plpgsql_adddatum((PLpgSQL_datum *) new);
***************
*** 1101,1107 ****

new = palloc(sizeof(PLpgSQL_recfield));
new->dtype = PLPGSQL_DTYPE_RECFIELD;
! new->fieldname = pstrdup(cp[2]);
new->recparentno = ns->itemno;

plpgsql_adddatum((PLpgSQL_datum *) new);
--- 1102,1109 ----

new = palloc(sizeof(PLpgSQL_recfield));
new->dtype = PLPGSQL_DTYPE_RECFIELD;
! new->fieldindex.fieldname = pstrdup(cp[2]);
! new->fieldindex_flag = RECFIELD_USE_FIELDNAME;
new->recparentno = ns->itemno;

plpgsql_adddatum((PLpgSQL_datum *) new);
***************
*** 1551,1556 ****
--- 1553,1738 ----
return T_DTYPE;
}

+ /* ----------
+ * plpgsql_parse_wordpercentword
+ * lookup associative index into record
+ * ----------
+ */
+ int
+ plpgsql_parse_wordpercentword(char *word)
+ {
+ PLpgSQL_nsitem *ns1, *ns2;
+ char *cp[2];
+ int ret = T_ERROR;
+
+ /* convert % to . for plpgsql_convert_ident */
+ char * percl = strchr(word, '%');
+ if ( percl == NULL )
+ return T_ERROR;
+ *percl = '.';
+
+ /* Do case conversion and word separation */
+ plpgsql_convert_ident(word, cp, 2);
+ *percl = '%';
+
+ /*
+ * Lookup the first word
+ */
+ ns1 = plpgsql_ns_lookup(cp[0], NULL);
+ if ( ns1 == NULL )
+ {
+ pfree(cp[0]);
+ pfree(cp[1]);
+ return T_ERROR;
+ }
+
+ ns2 = plpgsql_ns_lookup(cp[1], NULL);
+ pfree(cp[0]);
+ pfree(cp[1]);
+ if ( ns2 == NULL ) /* name lookup failed */
+ return T_ERROR;
+
+ switch (ns1->itemtype)
+ {
+ case PLPGSQL_NSTYPE_REC:
+ {
+ /*
+ * First word is a record name, so second word must be an
+ * variable holding the field name in this record.
+ */
+ if ( ns2->itemtype == PLPGSQL_NSTYPE_VAR ) {
+ PLpgSQL_recfield *new;
+
+ new = palloc(sizeof(PLpgSQL_recfield));
+ new->dtype = PLPGSQL_DTYPE_RECFIELD;
+ new->fieldindex.indexvar_no = ns2->itemno;
+ new->fieldindex_flag = RECFIELD_USE_INDEX_VAR;
+ new->recparentno = ns1->itemno;
+
+ plpgsql_adddatum((PLpgSQL_datum *) new);
+
+ plpgsql_yylval.scalar = (PLpgSQL_datum *) new;
+ ret = T_SCALAR;
+ }
+ break;
+ }
+ default:
+ break;
+ }
+ return ret;
+ }
+
+ /* ----------
+ * plpgsql_parse_wordnfields
+ * create # of fields in a record
+ * ----------
+ */
+ int
+ plpgsql_parse_wordnfields(char *word)
+ {
+ PLpgSQL_nsitem *ns1;
+ char *cp[2];
+ int ret = T_ERROR;
+
+ /* convert % to . for plpgsql_convert_ident */
+ int i = strlen(word) - 8;
+ Assert(word[i] == '%');
+ word[i] = '.';
+ /* Do case conversion and word separation */
+ plpgsql_convert_ident(word, cp, 2);
+ word[i] = '%';
+
+ /*
+ * Lookup the first word
+ */
+ ns1 = plpgsql_ns_lookup(cp[0], NULL);
+ if ( ns1 == NULL )
+ {
+ pfree(cp[0]);
+ pfree(cp[1]);
+ return T_ERROR;
+ }
+
+ pfree(cp[0]);
+ pfree(cp[1]);
+ switch (ns1->itemtype)
+ {
+ case PLPGSQL_NSTYPE_REC:
+ {
+ PLpgSQL_recfieldproperties *new;
+
+ new = palloc(sizeof(PLpgSQL_recfieldproperties));
+ new->dtype = PLPGSQL_DTYPE_NRECFIELD;
+ new->recparentno = ns1->itemno;
+ new->save_fieldnames = NULL;
+ plpgsql_adddatum((PLpgSQL_datum *) new);
+ plpgsql_yylval.scalar = (PLpgSQL_datum *) new;
+ ret = T_SCALAR; /* ??? */
+ break;
+ }
+ default:
+ break;
+ }
+ return ret;
+ }
+
+
+ /* ----------
+ * plpgsql_parse_wordfieldnames
+ * create fieldnames of a record
+ * ----------
+ */
+ int
+ plpgsql_parse_wordfieldnames(char *word)
+ {
+ PLpgSQL_nsitem *ns1;
+ char *cp[2];
+ int ret = T_ERROR;
+
+ /* convert % to . for plpgsql_convert_ident */
+ int i = strlen(word) - 11;
+ Assert(word[i] == '%');
+ word[i] = '.';
+ /* Do case conversion and word separation */
+ plpgsql_convert_ident(word, cp, 2);
+ word[i] = '%';
+
+ /*
+ * Lookup the first word
+ */
+ ns1 = plpgsql_ns_lookup(cp[0], NULL);
+ if ( ns1 == NULL )
+ {
+ pfree(cp[0]);
+ pfree(cp[1]);
+ return T_ERROR;
+ }
+
+ pfree(cp[0]);
+ pfree(cp[1]);
+
+ switch (ns1->itemtype)
+ {
+ case PLPGSQL_NSTYPE_REC:
+ {
+ PLpgSQL_recfieldproperties *new;
+
+ new = palloc(sizeof(PLpgSQL_recfieldproperties));
+ new->dtype = PLPGSQL_DTYPE_RECFIELDNAMES;
+ new->recparentno = ns1->itemno;
+ new->save_fieldnames = NULL;
+ plpgsql_adddatum((PLpgSQL_datum *) new);
+ plpgsql_yylval.scalar = (PLpgSQL_datum *) new;
+ ret = T_SCALAR; /* ??? */
+ break;
+ }
+ default:
+ break;
+ }
+ return ret;
+ }
+
+
/*
* plpgsql_build_variable - build a datum-array entry of a given
* datatype
*** ./src/pl/plpgsql/src/pl_exec.c.orig Wed Jul 13 19:22:08 2005
--- ./src/pl/plpgsql/src/pl_exec.c Thu Jul 14 23:54:28 2005
***************
*** 716,721 ****
--- 716,723 ----
case PLPGSQL_DTYPE_RECFIELD:
case PLPGSQL_DTYPE_ARRAYELEM:
case PLPGSQL_DTYPE_TRIGARG:
+ case PLPGSQL_DTYPE_NRECFIELD:
+ case PLPGSQL_DTYPE_RECFIELDNAMES:
/*
* These datum records are read-only at runtime, so no need
* to copy them
***************
*** 825,830 ****
--- 827,834 ----

case PLPGSQL_DTYPE_RECFIELD:
case PLPGSQL_DTYPE_ARRAYELEM:
+ case PLPGSQL_DTYPE_NRECFIELD:
+ case PLPGSQL_DTYPE_RECFIELDNAMES:
break;

default:
***************
*** 2146,2151 ****
--- 2150,2157 ----
static void
exec_eval_cleanup(PLpgSQL_execstate *estate)
{
+ int i;
+ ArrayType *a;
/* Clear result of a full SPI_execute */
if (estate->eval_tuptable != NULL)
SPI_freetuptable(estate->eval_tuptable);
***************
*** 2154,2159 ****
--- 2160,2173 ----
/* Clear result of exec_eval_simple_expr (but keep the econtext) */
if (estate->eval_econtext != NULL)
ResetExprContext(estate->eval_econtext);
+ for ( i = 0; i < estate->ndatums; ++i ) {
+ if ( estate->datums[i]->dtype == PLPGSQL_DTYPE_RECFIELDNAMES ) {
+ a = ((PLpgSQL_recfieldproperties *)(estate->datums[i]))->save_fieldnames;
+ if ( a )
+ pfree(a);
+ ((PLpgSQL_recfieldproperties *)(estate->datums[i]))->save_fieldnames = NULL;
+ }
+ }
}


***************
*** 3154,3165 ****
* Get the number of the records field to change and the
* number of attributes in the tuple.
*/
! fno = SPI_fnumber(rec->tupdesc, recfield->fieldname);
! if (fno == SPI_ERROR_NOATTRIBUTE)
ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\" has no field \"%s\"",
! rec->refname, recfield->fieldname)));
fno--;
natts = rec->tupdesc->natts;

--- 3168,3202 ----
* Get the number of the records field to change and the
* number of attributes in the tuple.
*/
! if ( recfield->fieldindex_flag == RECFIELD_USE_FIELDNAME ) {
! fno = SPI_fnumber(rec->tupdesc, recfield->fieldindex.fieldname);
! if (fno == SPI_ERROR_NOATTRIBUTE)
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\" has no field \"%s\"",
! rec->refname, recfield->fieldindex.fieldname)));
! }
! else if ( recfield->fieldindex_flag == RECFIELD_USE_INDEX_VAR ) {
! PLpgSQL_var * idxvar = (PLpgSQL_var *) (estate->datums[recfield->fieldindex.indexvar_no]);
! char * fname = convert_value_to_string(idxvar->value, idxvar->datatype->typoid);
! if ( fname == NULL )
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\": cannot evaluate variable to record index string",
! rec->refname)));
! fno = SPI_fnumber(rec->tupdesc, fname);
! pfree(fname);
! if (fno == SPI_ERROR_NOATTRIBUTE)
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\" has no field \"%s\"",
! rec->refname, fname)));
! }
! else
ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\": internal error",
! rec->refname)));
fno--;
natts = rec->tupdesc->natts;

***************
*** 3497,3515 ****
errmsg("record \"%s\" is not assigned yet",
rec->refname),
errdetail("The tuple structure of a not-yet-assigned record is indeterminate.")));
! fno = SPI_fnumber(rec->tupdesc, recfield->fieldname);
! if (fno == SPI_ERROR_NOATTRIBUTE)
ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\" has no field \"%s\"",
! rec->refname, recfield->fieldname)));
! *typeid = SPI_gettypeid(rec->tupdesc, fno);
! *value = SPI_getbinval(rec->tup, rec->tupdesc, fno, isnull);
! if (expectedtypeid != InvalidOid && expectedtypeid != *typeid)
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("type of \"%s.%s\" does not match that when preparing the plan",
! rec->refname, recfield->fieldname)));
break;
}

--- 3534,3677 ----
errmsg("record \"%s\" is not assigned yet",
rec->refname),
errdetail("The tuple structure of a not-yet-assigned record is indeterminate.")));
! if ( recfield->fieldindex_flag == RECFIELD_USE_FIELDNAME ) {
! fno = SPI_fnumber(rec->tupdesc, recfield->fieldindex.fieldname);
! if (fno == SPI_ERROR_NOATTRIBUTE)
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\" has no field \"%s\"",
! rec->refname, recfield->fieldindex.fieldname)));
! }
! else if ( recfield->fieldindex_flag == RECFIELD_USE_INDEX_VAR ) {
! PLpgSQL_var * idxvar = (PLpgSQL_var *) (estate->datums[recfield->fieldindex.indexvar_no]);
! char * fname = convert_value_to_string(idxvar->value, idxvar->datatype->typoid);
! if ( fname == NULL )
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\": cannot evaluate variable to record index string",
! rec->refname)));
! fno = SPI_fnumber(rec->tupdesc, fname);
! pfree(fname);
! if (fno == SPI_ERROR_NOATTRIBUTE)
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\" has no field \"%s\"",
! rec->refname, fname)));
! }
! else
ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\": internal error",
! rec->refname)));
!
! /* Do not allow typeids to become "narrowed" by InvalidOids
! causing specialized typeids from the tuple restricting the destination */
! if ( expectedtypeid != InvalidOid && expectedtypeid != SPI_gettypeid(rec->tupdesc, fno) ) {
! Datum cval = SPI_getbinval(rec->tup, rec->tupdesc, fno, isnull);
! cval = exec_simple_cast_value(cval,
! SPI_gettypeid(rec->tupdesc, fno),
! expectedtypeid,
! -1,
! isnull);
!
! *value = cval;
! *typeid = expectedtypeid;
! /* ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("type of \"%s\" does not match that when preparing the plan",
! rec->refname)));
! */
! }
! else { /* expected typeid matches */
! *value = SPI_getbinval(rec->tup, rec->tupdesc, fno, isnull);
! *typeid = SPI_gettypeid(rec->tupdesc, fno);
! }
! break;
! }
!
! case PLPGSQL_DTYPE_RECFIELDNAMES:
! /* Construct array datum from record field names */
! {
! Oid arraytypeid,
! arrayelemtypeid = TEXTOID;
! int16 arraytyplen,
! elemtyplen;
! bool elemtypbyval;
! char elemtypalign;
! ArrayType *arrayval;
! PLpgSQL_recfieldproperties * recfp = (PLpgSQL_recfieldproperties *) datum;
! PLpgSQL_rec *rec = (PLpgSQL_rec *) (estate->datums[recfp->recparentno]);
! int fc, tfc = 0;
! Datum *arrayelems;
! char *fieldname;
!
! if (!HeapTupleIsValid(rec->tup))
! ereport(ERROR,
! (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE) ,
! errmsg("record \"%s\" is not assigned yet",
! rec->refname),
! errdetail("The tuple structure of a not-yet-assigned record is indeterminate.")));
! arrayelems = palloc(sizeof(Datum) * rec->tupdesc->natts);
! arraytypeid = get_array_type(arrayelemtypeid);
! arraytyplen = get_typlen(arraytypeid);
! get_typlenbyvalalign(arrayelemtypeid,
! &elemtyplen,
! &elemtypbyval,
! &elemtypalign);
!
! if ( expectedtypeid != InvalidOid && expectedtypeid != arraytypeid )
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("type of \"%s\" does not match array type when preparing the plan",
! rec->refname)));
! for ( fc = 0; fc < rec->tupdesc->natts; ++fc ) {
! fieldname = SPI_fname(rec->tupdesc, fc+1);
! if ( fieldname ) {
! arrayelems[fc] = DirectFunctionCall1(textin, CStringGetDatum(fieldname));
! pfree(fieldname);
! ++tfc;
! }
! }
! arrayval = construct_array(arrayelems, tfc,
! arrayelemtypeid,
! elemtyplen,
! elemtypbyval,
! elemtypalign);
!
!
! /* construct_array copies data; free temp elem array */
! for ( fc = 0; fc < tfc; ++fc )
! pfree(DatumGetPointer(arrayelems[fc]));
! pfree(arrayelems);
! *value = PointerGetDatum(arrayval);
! *typeid = arraytypeid;
! *isnull = false;
! /* need to save the pointer because otherwise it does not get freed */
! if ( recfp->save_fieldnames )
! pfree(recfp->save_fieldnames);
! recfp->save_fieldnames = arrayval;
! break;
! }
!
! case PLPGSQL_DTYPE_NRECFIELD:
! /* Get # of fields in a record */
! {
! PLpgSQL_rec *rec = (PLpgSQL_rec *) (estate->datums[((PLpgSQL_recfieldproperties *) datum)->recparentno]);
!
! if (!HeapTupleIsValid(rec->tup))
! ereport(ERROR,
! (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE) ,
! errmsg("record \"%s\" is not assigned yet",
! rec->refname),
! errdetail("The tuple structure of a not-yet-assigned record is indeterminate.")));
! if ( expectedtypeid != InvalidOid && expectedtypeid != INT4OID )
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("type of \"%s\" does not match scalar type",
! rec->refname)));
! *value = Int32GetDatum(rec->tupdesc->natts);
! *typeid = INT4OID;
! *isnull = false;
break;
}

***************
*** 3610,3616 ****
*/
if (expr->plan == NULL)
exec_prepare_plan(estate, expr);
!
/*
* If this is a simple expression, bypass SPI and use the executor
* directly
--- 3772,3799 ----
*/
if (expr->plan == NULL)
exec_prepare_plan(estate, expr);
! else {
! /*
! * check for any subexpressions with varying type in the expression
! * currently, this is a record field of a record indexed by a variable
! */
! int i;
! PLpgSQL_datum *d;
! PLpgSQL_recfield *rf;
! for ( i = 0; i < expr->nparams; ++i ) {
! d = estate->datums[expr->params[i]];
! if ( d->dtype == PLPGSQL_DTYPE_RECFIELD ) {
! rf = (PLpgSQL_recfield *)d;
! if ( rf->fieldindex_flag == RECFIELD_USE_INDEX_VAR )
! break;
! }
! }
! if ( i < expr->nparams ) {
! /* discard the plan and get new one */
! SPI_freeplan(expr->plan);
! exec_prepare_plan(estate, expr);
! }
! }
/*
* If this is a simple expression, bypass SPI and use the executor
* directly
*** ./src/pl/plpgsql/src/pl_funcs.c.orig Wed Jul 13 19:23:08 2005
--- ./src/pl/plpgsql/src/pl_funcs.c Wed Jul 13 19:32:17 2005
***************
*** 1066,1074 ****
printf("REC %s\n", ((PLpgSQL_rec *) d)->refname);
break;
case PLPGSQL_DTYPE_RECFIELD:
! printf("RECFIELD %-16s of REC %d\n",
! ((PLpgSQL_recfield *) d)->fieldname,
! ((PLpgSQL_recfield *) d)->recparentno);
break;
case PLPGSQL_DTYPE_ARRAYELEM:
printf("ARRAYELEM of VAR %d subscript ",
--- 1066,1078 ----
printf("REC %s\n", ((PLpgSQL_rec *) d)->refname);
break;
case PLPGSQL_DTYPE_RECFIELD:
! if ( ((PLpgSQL_recfield *) d)->fieldindex_flag == RECFIELD_USE_FIELDNAME )
! printf("RECFIELD %-16s of REC %d\n",
! ((PLpgSQL_recfield *) d)->fieldindex.fieldname,
! ((PLpgSQL_recfield *) d)->recparentno);
! else
! printf("RECFIELD Variable of REC %d\n",
! ((PLpgSQL_recfield *) d)->recparentno);
break;
case PLPGSQL_DTYPE_ARRAYELEM:
printf("ARRAYELEM of VAR %d subscript ",
*** ./src/pl/plpgsql/src/plpgsql.h.orig Wed Jul 13 19:21:32 2005
--- ./src/pl/plpgsql/src/plpgsql.h Thu Jul 14 17:14:46 2005
***************
*** 73,79 ****
PLPGSQL_DTYPE_RECFIELD,
PLPGSQL_DTYPE_ARRAYELEM,
PLPGSQL_DTYPE_EXPR,
! PLPGSQL_DTYPE_TRIGARG
};

/* ----------
--- 73,81 ----
PLPGSQL_DTYPE_RECFIELD,
PLPGSQL_DTYPE_ARRAYELEM,
PLPGSQL_DTYPE_EXPR,
! PLPGSQL_DTYPE_TRIGARG,
! PLPGSQL_DTYPE_RECFIELDNAMES,
! PLPGSQL_DTYPE_NRECFIELD
};

/* ----------
***************
*** 269,278 ****
{ /* Field in record */
int dtype;
int rfno;
! char *fieldname;
int recparentno; /* dno of parent record */
} PLpgSQL_recfield;


typedef struct
{ /* Element of array variable */
--- 271,295 ----
{ /* Field in record */
int dtype;
int rfno;
! union {
! char *fieldname;
! int indexvar_no; /* dno of variable holding index string */
! } fieldindex;
! enum {
! RECFIELD_USE_FIELDNAME,
! RECFIELD_USE_INDEX_VAR,
! } fieldindex_flag;
int recparentno; /* dno of parent record */
} PLpgSQL_recfield;

+ typedef struct
+ { /* Field in record */
+ int dtype;
+ int rfno;
+ int recparentno; /* dno of parent record */
+ ArrayType * save_fieldnames;
+ } PLpgSQL_recfieldproperties;
+

typedef struct
{ /* Element of array variable */
***************
*** 678,683 ****
--- 695,703 ----
extern int plpgsql_parse_tripwordtype(char *word);
extern int plpgsql_parse_wordrowtype(char *word);
extern int plpgsql_parse_dblwordrowtype(char *word);
+ extern int plpgsql_parse_wordnfields(char *word);
+ extern int plpgsql_parse_wordfieldnames(char *word);
+ extern int plpgsql_parse_wordpercentword(char *word);
extern PLpgSQL_type *plpgsql_parse_datatype(const char *string);
extern PLpgSQL_type *plpgsql_build_datatype(Oid typeOid, int32 typmod);
extern PLpgSQL_variable *plpgsql_build_variable(const char *refname, int lineno,
*** ./src/pl/plpgsql/src/scan.l.orig Wed Jul 13 19:21:59 2005
--- ./src/pl/plpgsql/src/scan.l Wed Jul 13 19:33:30 2005
***************
*** 243,248 ****
--- 243,257 ----
{param}{space}*\.{space}*{identifier}{space}*%ROWT YPE {
plpgsql_error_lineno = plpgsql_scanner_lineno();
return plpgsql_parse_dblwordrowtype(yytext); }
+ {identifier}{space}*%NFIELDS {
+ plpgsql_error_lineno = plpgsql_scanner_lineno();
+ return plpgsql_parse_wordnfields(yytext); }
+ {identifier}{space}*%FIELDNAMES {
+ plpgsql_error_lineno = plpgsql_scanner_lineno();
+ return plpgsql_parse_wordfieldnames(yytext); }
+ {identifier}{space}*%{identifier} {
+ plpgsql_error_lineno = plpgsql_scanner_lineno();
+ return plpgsql_parse_wordpercentword(yytext); }

{digit}+ { return T_NUMBER; }

*** ./src/test/regress/expected/plpgsql.out.orig Thu Jul 14 12:03:36 2005
--- ./src/test/regress/expected/plpgsql.out Thu Jul 14 16:28:39 2005
***************
*** 2721,2723 ****
--- 2721,2764 ----
$$ language plpgsql;
ERROR: end label "outer_label" specified for unlabelled block
CONTEXT: compile of PL/pgSQL function "end_label4" near line 5
+ -- check introspective records
+ create table ritest (i INT4, t TEXT);
+ insert into ritest (i, t) VALUES (1, 'sometext');
+ create function test_record() returns void as $$
+ declare
+ cname text;
+ tval text;
+ ival int4;
+ tval2 text;
+ ival2 int4;
+ columns text[];
+ r RECORD;
+ begin
+ SELECT INTO r * FROM ritest WHERE i = 1;
+ ival := r.i;
+ tval := r.t;
+ RAISE NOTICE 'ival=%, tval=%', ival, tval;
+ cname := 'i';
+ ival2 := r%cname;
+ cname :='t';
+ tval2 := r%cname;
+ RAISE NOTICE 'ival2=%, tval2=%', ival2, tval2;
+ ival := r%NFIELDS;
+ RAISE NOTICE 'fieldcount=%', ival;
+ columns := r%FIELDNAMES;
+ RAISE NOTICE 'fieldnames=%', columns;
+ RETURN;
+ end;
+ $$ language plpgsql;
+ select test_record();
+ NOTICE: ival=1, tval=sometext
+ NOTICE: ival2=1, tval2=sometext
+ NOTICE: fieldcount=2
+ NOTICE: fieldnames={i,t}
+ test_record
+ -------------
+
+ (1 row)
+
+ drop table ritest;
+ drop function test_record();
*** ./src/test/regress/sql/plpgsql.sql.orig Thu Jul 14 10:57:17 2005
--- ./src/test/regress/sql/plpgsql.sql Thu Jul 14 12:17:26 2005
***************
*** 2280,2282 ****
--- 2280,2316 ----
end loop outer_label;
end;
$$ language plpgsql;
+
+ -- check introspective records
+ create table ritest (i INT4, t TEXT);
+ insert into ritest (i, t) VALUES (1, 'sometext');
+ create function test_record() returns void as $$
+ declare
+ cname text;
+ tval text;
+ ival int4;
+ tval2 text;
+ ival2 int4;
+ columns text[];
+ r RECORD;
+ begin
+ SELECT INTO r * FROM ritest WHERE i = 1;
+ ival := r.i;
+ tval := r.t;
+ RAISE NOTICE 'ival=%, tval=%', ival, tval;
+ cname := 'i';
+ ival2 := r%cname;
+ cname :='t';
+ tval2 := r%cname;
+ RAISE NOTICE 'ival2=%, tval2=%', ival2, tval2;
+ ival := r%NFIELDS;
+ RAISE NOTICE 'fieldcount=%', ival;
+ columns := r%FIELDNAMES;
+ RAISE NOTICE 'fieldnames=%', columns;
+ RETURN;
+ end;
+ $$ language plpgsql;
+ select test_record();
+ drop table ritest;
+ drop function test_record();
+


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Neil Conway schrieb:
> Titus von Boxberg wrote:
>
> Can you supply some proper regression tests, please? i.e. patch
> sql/plpgsql.sql and expected/plpgsql.out in src/test/regress
In sql/plpgsql.sql I have added a function testing the new features
and altered expected/plpgsql.out

>
> A few minor comments from skimming the patch:
Done.

------------------

The modifications to the first one are the following:
- restored type evaluation in RECFIELD value evaluation
in exec_eval_datum to the previous semantics.
The addition should now be completely invisible to existing code.
- instead do a check in exec_eval_expr() if the expression
contains datums of the type record%variable. For these
the type cannot be stored in a plan because the type might change
between evaluations of this expr. --> free and reprepare the plan.
- storage allocated by exec_eval_expr() for the expression
record%FIELDNAMES (the array containing the names)
is freed in exec_eval_cleanup

------------------

regression tests are ok on my system.

Do you agree with the mods and the patch?

Regards
Titus

*** ./doc/src/sgml/plpgsql.sgml.orig Thu Jul 14 00:49:33 2005
--- ./doc/src/sgml/plpgsql.sgml Thu Jul 14 01:15:41 2005
***************
*** 867,872 ****
--- 867,941 ----
</para>

<para>
+ To obtain the values of the fields the record is made up of,
+ the record variable can be qualified with the column or field
+ name. This can be done either literally with the usual dot notation
+ or the column name for indexing the record can be taken out of a scalar
+ variable. The syntax for this notation is Record_variable%IndexVariable.
+ To get information about the column fields, two special
+ notations exist that return all column names as an array (RecordVariable%FIELDNAMES)
+ and the count of fields stored in a record (RecordVariable%NFIELDS).
+ Thus, the RECORD can be viewed
+ as an associative array that allows for introspection of it's contents.
+ This feature is especially useful for writing generic triggers that
+ operate on records with unknown structure.
+ Here is an example that operates on the predefined record NEW in
+ a trigger procedure:
+ <programlisting>
+ CREATE OR REPLACE FUNCTION show_associative_records() RETURNS TRIGGER AS $ztt_u$
+ DECLARE
+ wmsg TEXT;
+ column TEXT;
+ colcontent TEXT;
+ colnames TEXT[];
+ coln INT4;
+ coli INT4;
+ testint8 INT8;
+ BEGIN
+ -- get the number of fields
+ coln := NEW%NFIELDS;
+ RAISE NOTICE 'Record type has % fields', coln;
+
+ -- obtain an array with all field names of the record
+ colnames := NEW%FIELDNAMES;
+ RAISE NOTICE 'All column names of test record: %', colnames;
+
+ -- show field names and contents of record
+ coli := 1;
+ LOOP
+ column := colnames[coli];
+ colcontent := NEW%column;
+ IF colcontent IS NULL
+ THEN
+ colcontent := '<NULL>';
+ END IF;
+ RAISE NOTICE 'column name % of NEW: value %', column, colcontent;
+ coli := coli + 1;
+ EXIT WHEN coli > coln;
+ END LOOP;
+
+ -- get a single record field value indexed by the contents of the variable "column"
+ -- raises an error if the record does not have the field name from "column"
+ column := 'SomeFieldNameInYourRecord';
+ -- testint8 will contain NULL if the value of this column
+ -- has a different type and cannot be casted to int8!
+ -- use a TEXT scalar variable to avoid this problem.
+ testint8 := NEW%column;
+ RAISE WARNING 'Column name = %, Column content = %', column, testint8;
+
+ -- the "normal" way with fixed field name with dot notation:
+ wmsg := NEW.SomeFieldNameInYourRecord;
+ RAISE NOTICE 'Column content NEW.SomeFieldNameInYourRecord = %', wmsg;
+
+ RETURN NULL;
+ END;
+ $ztt_u$ LANGUAGE plpgsql;
+

+
+ </programlisting>
+ </para>
+
+ <para>
Note that <literal>RECORD</> is not a true data type, only a placeholder.
One should also realize that when a <application>PL/pgSQL</application>
function is declared to return type <type>record</>, this is not quite the
*** ./src/pl/plpgsql/src/pl_comp.c.orig Wed Jul 13 19:22:43 2005
--- ./src/pl/plpgsql/src/pl_comp.c Thu Jul 14 23:54:26 2005
***************
*** 995,1001 ****

new = palloc(sizeof(PLpgSQL_recfield));
new->dtype = PLPGSQL_DTYPE_RECFIELD;
! new->fieldname = pstrdup(cp[1]);
new->recparentno = ns->itemno;

plpgsql_adddatum((PLpgSQL_datum *) new);
--- 995,1002 ----

new = palloc(sizeof(PLpgSQL_recfield));
new->dtype = PLPGSQL_DTYPE_RECFIELD;
! new->fieldindex.fieldname = pstrdup(cp[1]);
! new->fieldindex_flag = RECFIELD_USE_FIELDNAME;
new->recparentno = ns->itemno;

plpgsql_adddatum((PLpgSQL_datum *) new);
***************
*** 1101,1107 ****

new = palloc(sizeof(PLpgSQL_recfield));
new->dtype = PLPGSQL_DTYPE_RECFIELD;
! new->fieldname = pstrdup(cp[2]);
new->recparentno = ns->itemno;

plpgsql_adddatum((PLpgSQL_datum *) new);
--- 1102,1109 ----

new = palloc(sizeof(PLpgSQL_recfield));
new->dtype = PLPGSQL_DTYPE_RECFIELD;
! new->fieldindex.fieldname = pstrdup(cp[2]);
! new->fieldindex_flag = RECFIELD_USE_FIELDNAME;
new->recparentno = ns->itemno;

plpgsql_adddatum((PLpgSQL_datum *) new);
***************
*** 1551,1556 ****
--- 1553,1738 ----
return T_DTYPE;
}

+ /* ----------
+ * plpgsql_parse_wordpercentword
+ * lookup associative index into record
+ * ----------
+ */
+ int
+ plpgsql_parse_wordpercentword(char *word)
+ {
+ PLpgSQL_nsitem *ns1, *ns2;
+ char *cp[2];
+ int ret = T_ERROR;
+
+ /* convert % to . for plpgsql_convert_ident */
+ char * percl = strchr(word, '%');
+ if ( percl == NULL )
+ return T_ERROR;
+ *percl = '.';
+
+ /* Do case conversion and word separation */
+ plpgsql_convert_ident(word, cp, 2);
+ *percl = '%';
+
+ /*
+ * Lookup the first word
+ */
+ ns1 = plpgsql_ns_lookup(cp[0], NULL);
+ if ( ns1 == NULL )
+ {
+ pfree(cp[0]);
+ pfree(cp[1]);
+ return T_ERROR;
+ }
+
+ ns2 = plpgsql_ns_lookup(cp[1], NULL);
+ pfree(cp[0]);
+ pfree(cp[1]);
+ if ( ns2 == NULL ) /* name lookup failed */
+ return T_ERROR;
+
+ switch (ns1->itemtype)
+ {
+ case PLPGSQL_NSTYPE_REC:
+ {
+ /*
+ * First word is a record name, so second word must be an
+ * variable holding the field name in this record.
+ */
+ if ( ns2->itemtype == PLPGSQL_NSTYPE_VAR ) {
+ PLpgSQL_recfield *new;
+
+ new = palloc(sizeof(PLpgSQL_recfield));
+ new->dtype = PLPGSQL_DTYPE_RECFIELD;
+ new->fieldindex.indexvar_no = ns2->itemno;
+ new->fieldindex_flag = RECFIELD_USE_INDEX_VAR;
+ new->recparentno = ns1->itemno;
+
+ plpgsql_adddatum((PLpgSQL_datum *) new);
+
+ plpgsql_yylval.scalar = (PLpgSQL_datum *) new;
+ ret = T_SCALAR;
+ }
+ break;
+ }
+ default:
+ break;
+ }
+ return ret;
+ }
+
+ /* ----------
+ * plpgsql_parse_wordnfields
+ * create # of fields in a record
+ * ----------
+ */
+ int
+ plpgsql_parse_wordnfields(char *word)
+ {
+ PLpgSQL_nsitem *ns1;
+ char *cp[2];
+ int ret = T_ERROR;
+
+ /* convert % to . for plpgsql_convert_ident */
+ int i = strlen(word) - 8;
+ Assert(word[i] == '%');
+ word[i] = '.';
+ /* Do case conversion and word separation */
+ plpgsql_convert_ident(word, cp, 2);
+ word[i] = '%';
+
+ /*
+ * Lookup the first word
+ */
+ ns1 = plpgsql_ns_lookup(cp[0], NULL);
+ if ( ns1 == NULL )
+ {
+ pfree(cp[0]);
+ pfree(cp[1]);
+ return T_ERROR;
+ }
+
+ pfree(cp[0]);
+ pfree(cp[1]);
+ switch (ns1->itemtype)
+ {
+ case PLPGSQL_NSTYPE_REC:
+ {
+ PLpgSQL_recfieldproperties *new;
+
+ new = palloc(sizeof(PLpgSQL_recfieldproperties));
+ new->dtype = PLPGSQL_DTYPE_NRECFIELD;
+ new->recparentno = ns1->itemno;
+ new->save_fieldnames = NULL;
+ plpgsql_adddatum((PLpgSQL_datum *) new);
+ plpgsql_yylval.scalar = (PLpgSQL_datum *) new;
+ ret = T_SCALAR; /* ??? */
+ break;
+ }
+ default:
+ break;
+ }
+ return ret;
+ }
+
+
+ /* ----------
+ * plpgsql_parse_wordfieldnames
+ * create fieldnames of a record
+ * ----------
+ */
+ int
+ plpgsql_parse_wordfieldnames(char *word)
+ {
+ PLpgSQL_nsitem *ns1;
+ char *cp[2];
+ int ret = T_ERROR;
+
+ /* convert % to . for plpgsql_convert_ident */
+ int i = strlen(word) - 11;
+ Assert(word[i] == '%');
+ word[i] = '.';
+ /* Do case conversion and word separation */
+ plpgsql_convert_ident(word, cp, 2);
+ word[i] = '%';
+
+ /*
+ * Lookup the first word
+ */
+ ns1 = plpgsql_ns_lookup(cp[0], NULL);
+ if ( ns1 == NULL )
+ {
+ pfree(cp[0]);
+ pfree(cp[1]);
+ return T_ERROR;
+ }
+
+ pfree(cp[0]);
+ pfree(cp[1]);
+
+ switch (ns1->itemtype)
+ {
+ case PLPGSQL_NSTYPE_REC:
+ {
+ PLpgSQL_recfieldproperties *new;
+
+ new = palloc(sizeof(PLpgSQL_recfieldproperties));
+ new->dtype = PLPGSQL_DTYPE_RECFIELDNAMES;
+ new->recparentno = ns1->itemno;
+ new->save_fieldnames = NULL;
+ plpgsql_adddatum((PLpgSQL_datum *) new);
+ plpgsql_yylval.scalar = (PLpgSQL_datum *) new;
+ ret = T_SCALAR; /* ??? */
+ break;
+ }
+ default:
+ break;
+ }
+ return ret;
+ }
+
+
/*
* plpgsql_build_variable - build a datum-array entry of a given
* datatype
*** ./src/pl/plpgsql/src/pl_exec.c.orig Wed Jul 13 19:22:08 2005
--- ./src/pl/plpgsql/src/pl_exec.c Thu Jul 14 23:54:28 2005
***************
*** 716,721 ****
--- 716,723 ----
case PLPGSQL_DTYPE_RECFIELD:
case PLPGSQL_DTYPE_ARRAYELEM:
case PLPGSQL_DTYPE_TRIGARG:
+ case PLPGSQL_DTYPE_NRECFIELD:
+ case PLPGSQL_DTYPE_RECFIELDNAMES:
/*
* These datum records are read-only at runtime, so no need
* to copy them
***************
*** 825,830 ****
--- 827,834 ----

case PLPGSQL_DTYPE_RECFIELD:
case PLPGSQL_DTYPE_ARRAYELEM:
+ case PLPGSQL_DTYPE_NRECFIELD:
+ case PLPGSQL_DTYPE_RECFIELDNAMES:
break;

default:
***************
*** 2146,2151 ****
--- 2150,2157 ----
static void
exec_eval_cleanup(PLpgSQL_execstate *estate)
{
+ int i;
+ ArrayType *a;
/* Clear result of a full SPI_execute */
if (estate->eval_tuptable != NULL)
SPI_freetuptable(estate->eval_tuptable);
***************
*** 2154,2159 ****
--- 2160,2173 ----
/* Clear result of exec_eval_simple_expr (but keep the econtext) */
if (estate->eval_econtext != NULL)
ResetExprContext(estate->eval_econtext);
+ for ( i = 0; i < estate->ndatums; ++i ) {
+ if ( estate->datums[i]->dtype == PLPGSQL_DTYPE_RECFIELDNAMES ) {
+ a = ((PLpgSQL_recfieldproperties *)(estate->datums[i]))->save_fieldnames;
+ if ( a )
+ pfree(a);
+ ((PLpgSQL_recfieldproperties *)(estate->datums[i]))->save_fieldnames = NULL;
+ }
+ }
}


***************
*** 3154,3165 ****
* Get the number of the records field to change and the
* number of attributes in the tuple.
*/
! fno = SPI_fnumber(rec->tupdesc, recfield->fieldname);
! if (fno == SPI_ERROR_NOATTRIBUTE)
ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\" has no field \"%s\"",
! rec->refname, recfield->fieldname)));
fno--;
natts = rec->tupdesc->natts;

--- 3168,3202 ----
* Get the number of the records field to change and the
* number of attributes in the tuple.
*/
! if ( recfield->fieldindex_flag == RECFIELD_USE_FIELDNAME ) {
! fno = SPI_fnumber(rec->tupdesc, recfield->fieldindex.fieldname);
! if (fno == SPI_ERROR_NOATTRIBUTE)
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\" has no field \"%s\"",
! rec->refname, recfield->fieldindex.fieldname)));
! }
! else if ( recfield->fieldindex_flag == RECFIELD_USE_INDEX_VAR ) {
! PLpgSQL_var * idxvar = (PLpgSQL_var *) (estate->datums[recfield->fieldindex.indexvar_no]);
! char * fname = convert_value_to_string(idxvar->value, idxvar->datatype->typoid);
! if ( fname == NULL )
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\": cannot evaluate variable to record index string",
! rec->refname)));
! fno = SPI_fnumber(rec->tupdesc, fname);
! pfree(fname);
! if (fno == SPI_ERROR_NOATTRIBUTE)
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\" has no field \"%s\"",
! rec->refname, fname)));
! }
! else
ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\": internal error",
! rec->refname)));
fno--;
natts = rec->tupdesc->natts;

***************
*** 3497,3515 ****
errmsg("record \"%s\" is not assigned yet",
rec->refname),
errdetail("The tuple structure of a not-yet-assigned record is indeterminate.")));
! fno = SPI_fnumber(rec->tupdesc, recfield->fieldname);
! if (fno == SPI_ERROR_NOATTRIBUTE)
ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\" has no field \"%s\"",
! rec->refname, recfield->fieldname)));
! *typeid = SPI_gettypeid(rec->tupdesc, fno);
! *value = SPI_getbinval(rec->tup, rec->tupdesc, fno, isnull);
! if (expectedtypeid != InvalidOid && expectedtypeid != *typeid)
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("type of \"%s.%s\" does not match that when preparing the plan",
! rec->refname, recfield->fieldname)));
break;
}

--- 3534,3677 ----
errmsg("record \"%s\" is not assigned yet",
rec->refname),
errdetail("The tuple structure of a not-yet-assigned record is indeterminate.")));
! if ( recfield->fieldindex_flag == RECFIELD_USE_FIELDNAME ) {
! fno = SPI_fnumber(rec->tupdesc, recfield->fieldindex.fieldname);
! if (fno == SPI_ERROR_NOATTRIBUTE)
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\" has no field \"%s\"",
! rec->refname, recfield->fieldindex.fieldname)));
! }
! else if ( recfield->fieldindex_flag == RECFIELD_USE_INDEX_VAR ) {
! PLpgSQL_var * idxvar = (PLpgSQL_var *) (estate->datums[recfield->fieldindex.indexvar_no]);
! char * fname = convert_value_to_string(idxvar->value, idxvar->datatype->typoid);
! if ( fname == NULL )
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\": cannot evaluate variable to record index string",
! rec->refname)));
! fno = SPI_fnumber(rec->tupdesc, fname);
! pfree(fname);
! if (fno == SPI_ERROR_NOATTRIBUTE)
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\" has no field \"%s\"",
! rec->refname, fname)));
! }
! else
ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("record \"%s\": internal error",
! rec->refname)));
!
! /* Do not allow typeids to become "narrowed" by InvalidOids
! causing specialized typeids from the tuple restricting the destination */
! if ( expectedtypeid != InvalidOid && expectedtypeid != SPI_gettypeid(rec->tupdesc, fno) ) {
! Datum cval = SPI_getbinval(rec->tup, rec->tupdesc, fno, isnull);
! cval = exec_simple_cast_value(cval,
! SPI_gettypeid(rec->tupdesc, fno),
! expectedtypeid,
! -1,
! isnull);
!
! *value = cval;
! *typeid = expectedtypeid;
! /* ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("type of \"%s\" does not match that when preparing the plan",
! rec->refname)));
! */
! }
! else { /* expected typeid matches */
! *value = SPI_getbinval(rec->tup, rec->tupdesc, fno, isnull);
! *typeid = SPI_gettypeid(rec->tupdesc, fno);
! }
! break;
! }
!
! case PLPGSQL_DTYPE_RECFIELDNAMES:
! /* Construct array datum from record field names */
! {
! Oid arraytypeid,
! arrayelemtypeid = TEXTOID;
! int16 arraytyplen,
! elemtyplen;
! bool elemtypbyval;
! char elemtypalign;
! ArrayType *arrayval;
! PLpgSQL_recfieldproperties * recfp = (PLpgSQL_recfieldproperties *) datum;
! PLpgSQL_rec *rec = (PLpgSQL_rec *) (estate->datums[recfp->recparentno]);
! int fc, tfc = 0;
! Datum *arrayelems;
! char *fieldname;
!
! if (!HeapTupleIsValid(rec->tup))
! ereport(ERROR,
! (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE) ,
! errmsg("record \"%s\" is not assigned yet",
! rec->refname),
! errdetail("The tuple structure of a not-yet-assigned record is indeterminate.")));
! arrayelems = palloc(sizeof(Datum) * rec->tupdesc->natts);
! arraytypeid = get_array_type(arrayelemtypeid);
! arraytyplen = get_typlen(arraytypeid);
! get_typlenbyvalalign(arrayelemtypeid,
! &elemtyplen,
! &elemtypbyval,
! &elemtypalign);
!
! if ( expectedtypeid != InvalidOid && expectedtypeid != arraytypeid )
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("type of \"%s\" does not match array type when preparing the plan",
! rec->refname)));
! for ( fc = 0; fc < rec->tupdesc->natts; ++fc ) {
! fieldname = SPI_fname(rec->tupdesc, fc+1);
! if ( fieldname ) {
! arrayelems[fc] = DirectFunctionCall1(textin, CStringGetDatum(fieldname));
! pfree(fieldname);
! ++tfc;
! }
! }
! arrayval = construct_array(arrayelems, tfc,
! arrayelemtypeid,
! elemtyplen,
! elemtypbyval,
! elemtypalign);
!
!
! /* construct_array copies data; free temp elem array */
! for ( fc = 0; fc < tfc; ++fc )
! pfree(DatumGetPointer(arrayelems[fc]));
! pfree(arrayelems);
! *value = PointerGetDatum(arrayval);
! *typeid = arraytypeid;
! *isnull = false;
! /* need to save the pointer because otherwise it does not get freed */
! if ( recfp->save_fieldnames )
! pfree(recfp->save_fieldnames);
! recfp->save_fieldnames = arrayval;
! break;
! }
!
! case PLPGSQL_DTYPE_NRECFIELD:
! /* Get # of fields in a record */
! {
! PLpgSQL_rec *rec = (PLpgSQL_rec *) (estate->datums[((PLpgSQL_recfieldproperties *) datum)->recparentno]);
!
! if (!HeapTupleIsValid(rec->tup))
! ereport(ERROR,
! (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE) ,
! errmsg("record \"%s\" is not assigned yet",
! rec->refname),
! errdetail("The tuple structure of a not-yet-assigned record is indeterminate.")));
! if ( expectedtypeid != InvalidOid && expectedtypeid != INT4OID )
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("type of \"%s\" does not match scalar type",
! rec->refname)));
! *value = Int32GetDatum(rec->tupdesc->natts);
! *typeid = INT4OID;
! *isnull = false;
break;
}

***************
*** 3610,3616 ****
*/
if (expr->plan == NULL)
exec_prepare_plan(estate, expr);
!
/*
* If this is a simple expression, bypass SPI and use the executor
* directly
--- 3772,3799 ----
*/
if (expr->plan == NULL)
exec_prepare_plan(estate, expr);
! else {
! /*
! * check for any subexpressions with varying type in the expression
! * currently, this is a record field of a record indexed by a variable
! */
! int i;
! PLpgSQL_datum *d;
! PLpgSQL_recfield *rf;
! for ( i = 0; i < expr->nparams; ++i ) {
! d = estate->datums[expr->params[i]];
! if ( d->dtype == PLPGSQL_DTYPE_RECFIELD ) {
! rf = (PLpgSQL_recfield *)d;
! if ( rf->fieldindex_flag == RECFIELD_USE_INDEX_VAR )
! break;
! }
! }
! if ( i < expr->nparams ) {
! /* discard the plan and get new one */
! SPI_freeplan(expr->plan);
! exec_prepare_plan(estate, expr);
! }
! }
/*
* If this is a simple expression, bypass SPI and use the executor
* directly
*** ./src/pl/plpgsql/src/pl_funcs.c.orig Wed Jul 13 19:23:08 2005
--- ./src/pl/plpgsql/src/pl_funcs.c Wed Jul 13 19:32:17 2005
***************
*** 1066,1074 ****
printf("REC %s\n", ((PLpgSQL_rec *) d)->refname);
break;
case PLPGSQL_DTYPE_RECFIELD:
! printf("RECFIELD %-16s of REC %d\n",
! ((PLpgSQL_recfield *) d)->fieldname,
! ((PLpgSQL_recfield *) d)->recparentno);
break;
case PLPGSQL_DTYPE_ARRAYELEM:
printf("ARRAYELEM of VAR %d subscript ",
--- 1066,1078 ----
printf("REC %s\n", ((PLpgSQL_rec *) d)->refname);
break;
case PLPGSQL_DTYPE_RECFIELD:
! if ( ((PLpgSQL_recfield *) d)->fieldindex_flag == RECFIELD_USE_FIELDNAME )
! printf("RECFIELD %-16s of REC %d\n",
! ((PLpgSQL_recfield *) d)->fieldindex.fieldname,
! ((PLpgSQL_recfield *) d)->recparentno);
! else
! printf("RECFIELD Variable of REC %d\n",
! ((PLpgSQL_recfield *) d)->recparentno);
break;
case PLPGSQL_DTYPE_ARRAYELEM:
printf("ARRAYELEM of VAR %d subscript ",
*** ./src/pl/plpgsql/src/plpgsql.h.orig Wed Jul 13 19:21:32 2005
--- ./src/pl/plpgsql/src/plpgsql.h Thu Jul 14 17:14:46 2005
***************
*** 73,79 ****
PLPGSQL_DTYPE_RECFIELD,
PLPGSQL_DTYPE_ARRAYELEM,
PLPGSQL_DTYPE_EXPR,
! PLPGSQL_DTYPE_TRIGARG
};

/* ----------
--- 73,81 ----
PLPGSQL_DTYPE_RECFIELD,
PLPGSQL_DTYPE_ARRAYELEM,
PLPGSQL_DTYPE_EXPR,
! PLPGSQL_DTYPE_TRIGARG,
! PLPGSQL_DTYPE_RECFIELDNAMES,
! PLPGSQL_DTYPE_NRECFIELD
};

/* ----------
***************
*** 269,278 ****
{ /* Field in record */
int dtype;
int rfno;
! char *fieldname;
int recparentno; /* dno of parent record */
} PLpgSQL_recfield;


typedef struct
{ /* Element of array variable */
--- 271,295 ----
{ /* Field in record */
int dtype;
int rfno;
! union {
! char *fieldname;
! int indexvar_no; /* dno of variable hold