<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.91 2006/05/30 11:54:51 momjian Exp $ -->

<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 by literally using the column name

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 field names of the record,

a special expression exists that returns all column names as an array:

RecordVariable.(*) .

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 procedure that shows column names and values

of the predefined record NEW in a trigger procedure:

<programlisting>

CREATE OR REPLACE FUNCTION show_associative_records() RETURNS TRIGGER AS $$

DECLARE

colname TEXT;

colcontent TEXT;

colnames TEXT[];

coln INT4;

coli INT4;

BEGIN

-- obtain an array with all field names of the record

colnames := NEW.(*);

RAISE NOTICE 'All column names of test record: %', colnames;

-- show field names and contents of record

coli := 1;

coln := array_upper(colnames,1);

RAISE NOTICE 'Number of columns in NEW: %', coln;

FOR coli IN 1 .. coln LOOP

colname := colnames[coli];

colcontent := NEW.(colname);

RAISE NOTICE 'column % of NEW: %', quote_ident(colname), quote_literal(colcontent);

END LOOP;

-- Do it with a fixed field name:

-- will have to know the column name

RAISE NOTICE 'column someint of NEW: %', quote_literal(NEW.someint);

RETURN NULL;

END;

$$ LANGUAGE plpgsql;

--CREATE TABLE test_records (someint INT8, somestring TEXT);

--CREATE TRIGGER tr_test_record BEFORE INSERT ON test_records FOR EACH ROW EXECUTE PROCEDURE show_associative_records();

</programlisting>

</para>

new->fieldindex.fieldname = pstrdup(cp[1]);

new->fieldindex_flag = RECFIELD_USE_FIELDNAME;

new->fieldindex.fieldname = pstrdup(cp[2]);

new->fieldindex_flag = RECFIELD_USE_FIELDNAME;

plpgsql_parse_recindex(char *word)

{

PLpgSQL_nsitem *ns1, *ns2;

char *cp[2];

int ret = T_ERROR;

char *fieldvar;

int fl;

/* Do case conversion and word separation */

plpgsql_convert_ident(word, cp, 2);

Assert(cp[1] != NULL);

/* cleanup the "(identifier)" string to "identifier" */

fieldvar = cp[1];

Assert(*fieldvar == '(');

++fieldvar; /* get rid of ( */

fl = strlen(fieldvar);

Assert(fieldvar[fl-1] == ')');

fieldvar[fl-1] = 0; /* get rid of ) */

/*

ns1 = plpgsql_ns_lookup(cp[0], NULL);

if ( ns1 == NULL )

{

pfree(cp[0]);

pfree(cp[1]);

return T_ERROR;

}

ns2 = plpgsql_ns_lookup(fieldvar, NULL);

pfree(cp[0]);

pfree(cp[1]);

if ( ns2 == NULL ) /* name lookup failed */

return T_ERROR;

switch (ns1->itemtype)

{

case PLPGSQL_NSTYPE_REC:

{

/*

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_recfieldnames(char *word)

{

PLpgSQL_nsitem *ns1;

char *cp[2];

int ret = T_ERROR;

/* Do case conversion and word separation */

plpgsql_convert_ident(word, cp, 2);

/*

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;

}