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

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

<chapter id="plpgsql">

<title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>

field in it will draw a run-time error.

</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 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

colnameTEXT;

colcontentTEXT;

colnamesTEXT[];

colnINT4;

coliINT4;

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>

<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>

new=palloc(sizeof(PLpgSQL_recfield));

new->dtype=PLPGSQL_DTYPE_RECFIELD;

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

new->fieldindex_flag=RECFIELD_USE_FIELDNAME;

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

new->recparentno=ns->itemno;

plpgsql_adddatum((PLpgSQL_datum*)new);

new=palloc(sizeof(PLpgSQL_recfield));

new->dtype=PLPGSQL_DTYPE_RECFIELD;

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

new->fieldindex_flag=RECFIELD_USE_FIELDNAME;

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

new->recparentno=ns->itemno;

plpgsql_adddatum((PLpgSQL_datum*)new);

returnT_DTYPE;

}

plpgsql_parse_recindex(char*word)

{

PLpgSQL_nsitem*ns1,*ns2;

char*cp[2];

intret=T_ERROR;

char*fieldvar;

intfl;

plpgsql_convert_ident(word,cp,2);

Assert(cp[1]!=NULL);

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]);

returnT_ERROR;

}

ns2=plpgsql_ns_lookup(fieldvar,NULL);

pfree(cp[0]);

pfree(cp[1]);

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

returnT_ERROR;

switch (ns1->itemtype)

{

casePLPGSQL_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;

}

returnret;

}

plpgsql_parse_recfieldnames(char*word)

{

PLpgSQL_nsitem*ns1;

char*cp[2];

intret=T_ERROR;

plpgsql_convert_ident(word,cp,2);

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

if (ns1==NULL )

{

pfree(cp[0]);

pfree(cp[1]);

returnT_ERROR;

}

pfree(cp[0]);

pfree(cp[1]);

switch (ns1->itemtype)

{

casePLPGSQL_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;

}

returnret;

}