Skip to content

Commit 38c7700

Browse files
bmomjianbmomjian
committed
Add dynamic record inspection to PL/PgSQL, useful for generic triggers:
 tval2 := r.(cname); or columns := r.(*); Titus von Boxberg
1 parent 88ba64d commit 38c7700

File tree

8 files changed

+472
-38
lines changed

8 files changed

+472
-38
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 50 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.92 2006/05/30 11:58:05 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.93 2006/05/30 12:03:12 momjian Exp $ -->
22

33
<chapter id="plpgsql">
44
<title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
@@ -879,6 +879,55 @@ SELECT merge_fields(t.*) FROM table1 t WHERE ... ;
879879
field in it will draw a run-time error.
880880
</para>
881881

882+
<para>
883+
To obtain the values of the fields the record is made up of,
884+
the record variable can be qualified with the column or field
885+
name. This can be done either by literally using the column name
886+
or the column name for indexing the record can be taken out of a scalar
887+
variable. The syntax for this notation is Record_variable.(IndexVariable).
888+
To get information about the column field names of the record,
889+
a special expression exists that returns all column names as an array:
890+
RecordVariable.(*) .
891+
Thus, the RECORD can be viewed
892+
as an associative array that allows for introspection of it's contents.
893+
This feature is especially useful for writing generic triggers that
894+
operate on records with unknown structure.
895+
Here is an example procedure that shows column names and values
896+
of the predefined record NEW in a trigger procedure:
897+
<programlisting>
898+
899+
CREATE OR REPLACE FUNCTION show_associative_records() RETURNS TRIGGER AS $$
900+
DECLARE
901+
colname TEXT;
902+
colcontent TEXT;
903+
colnames TEXT[];
904+
coln INT4;
905+
coli INT4;
906+
BEGIN
907+
-- obtain an array with all field names of the record
908+
colnames := NEW.(*);
909+
RAISE NOTICE 'All column names of test record: %', colnames;
910+
-- show field names and contents of record
911+
coli := 1;
912+
coln := array_upper(colnames,1);
913+
RAISE NOTICE 'Number of columns in NEW: %', coln;
914+
FOR coli IN 1 .. coln LOOP
915+
colname := colnames[coli];
916+
colcontent := NEW.(colname);
917+
RAISE NOTICE 'column % of NEW: %', quote_ident(colname), quote_literal(colcontent);
918+
END LOOP;
919+
-- Do it with a fixed field name:
920+
-- will have to know the column name
921+
RAISE NOTICE 'column someint of NEW: %', quote_literal(NEW.someint);
922+
RETURN NULL;
923+
END;
924+
$$ LANGUAGE plpgsql;
925+
--CREATE TABLE test_records (someint INT8, somestring TEXT);
926+
--CREATE TRIGGER tr_test_record BEFORE INSERT ON test_records FOR EACH ROW EXECUTE PROCEDURE show_associative_records();
927+
928+
</programlisting>
929+
</para>
930+
882931
<para>
883932
Note that <literal>RECORD</> is not a true data type, only a placeholder.
884933
One should also realize that when a <application>PL/pgSQL</application>

‎src/pl/plpgsql/src/pl_comp.c

Lines changed: 131 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_comp.c,v 1.104 2006/05/30 11:58:05 momjian Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_comp.c,v 1.105 2006/05/30 12:03:13 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -884,7 +884,8 @@ plpgsql_parse_dblword(char *word)
884884

885885
new = palloc(sizeof(PLpgSQL_recfield));
886886
new->dtype = PLPGSQL_DTYPE_RECFIELD;
887-
new->fieldname = pstrdup(cp[1]);
887+
new->fieldindex.fieldname = pstrdup(cp[1]);
888+
new->fieldindex_flag = RECFIELD_USE_FIELDNAME;
888889
new->recparentno = ns->itemno;
889890

890891
plpgsql_adddatum((PLpgSQL_datum *) new);
@@ -990,7 +991,8 @@ plpgsql_parse_tripword(char *word)
990991

991992
new = palloc(sizeof(PLpgSQL_recfield));
992993
new->dtype = PLPGSQL_DTYPE_RECFIELD;
993-
new->fieldname = pstrdup(cp[2]);
994+
new->fieldindex.fieldname = pstrdup(cp[2]);
995+
new->fieldindex_flag = RECFIELD_USE_FIELDNAME;
994996
new->recparentno = ns->itemno;
995997

996998
plpgsql_adddatum((PLpgSQL_datum *) new);
@@ -1438,6 +1440,132 @@ plpgsql_parse_dblwordrowtype(char *word)
14381440
return T_DTYPE;
14391441
}
14401442

1443+
/* ----------
1444+
* plpgsql_parse_recindex
1445+
* lookup associative index into record
1446+
* ----------
1447+
*/
1448+
int
1449+
plpgsql_parse_recindex(char *word)
1450+
{
1451+
PLpgSQL_nsitem *ns1, *ns2;
1452+
char *cp[2];
1453+
int ret = T_ERROR;
1454+
char *fieldvar;
1455+
int fl;
1456+
1457+
/* Do case conversion and word separation */
1458+
plpgsql_convert_ident(word, cp, 2);
1459+
Assert(cp[1] != NULL);
1460+
1461+
/* cleanup the "(identifier)" string to "identifier" */
1462+
fieldvar = cp[1];
1463+
Assert(*fieldvar == '(');
1464+
++fieldvar; /* get rid of ( */
1465+
1466+
fl = strlen(fieldvar);
1467+
Assert(fieldvar[fl-1] == ')');
1468+
fieldvar[fl-1] = 0; /* get rid of ) */
1469+
1470+
/*
1471+
* Lookup the first word
1472+
*/
1473+
ns1 = plpgsql_ns_lookup(cp[0], NULL);
1474+
if ( ns1 == NULL )
1475+
{
1476+
pfree(cp[0]);
1477+
pfree(cp[1]);
1478+
return T_ERROR;
1479+
}
1480+
1481+
ns2 = plpgsql_ns_lookup(fieldvar, NULL);
1482+
pfree(cp[0]);
1483+
pfree(cp[1]);
1484+
if ( ns2 == NULL ) /* name lookup failed */
1485+
return T_ERROR;
1486+
1487+
switch (ns1->itemtype)
1488+
{
1489+
case PLPGSQL_NSTYPE_REC:
1490+
{
1491+
/*
1492+
* First word is a record name, so second word must be an
1493+
* variable holding the field name in this record.
1494+
*/
1495+
if ( ns2->itemtype == PLPGSQL_NSTYPE_VAR ) {
1496+
PLpgSQL_recfield *new;
1497+
1498+
new = palloc(sizeof(PLpgSQL_recfield));
1499+
new->dtype = PLPGSQL_DTYPE_RECFIELD;
1500+
new->fieldindex.indexvar_no = ns2->itemno;
1501+
new->fieldindex_flag = RECFIELD_USE_INDEX_VAR;
1502+
new->recparentno = ns1->itemno;
1503+
1504+
plpgsql_adddatum((PLpgSQL_datum *) new);
1505+
1506+
plpgsql_yylval.scalar = (PLpgSQL_datum *) new;
1507+
ret = T_SCALAR;
1508+
}
1509+
break;
1510+
}
1511+
default:
1512+
break;
1513+
}
1514+
return ret;
1515+
}
1516+
1517+
1518+
/* ----------
1519+
* plpgsql_parse_recfieldnames
1520+
* create fieldnames of a record
1521+
* ----------
1522+
*/
1523+
int
1524+
plpgsql_parse_recfieldnames(char *word)
1525+
{
1526+
PLpgSQL_nsitem *ns1;
1527+
char *cp[2];
1528+
int ret = T_ERROR;
1529+
1530+
/* Do case conversion and word separation */
1531+
plpgsql_convert_ident(word, cp, 2);
1532+
1533+
/*
1534+
* Lookup the first word
1535+
*/
1536+
ns1 = plpgsql_ns_lookup(cp[0], NULL);
1537+
if ( ns1 == NULL )
1538+
{
1539+
pfree(cp[0]);
1540+
pfree(cp[1]);
1541+
return T_ERROR;
1542+
}
1543+
1544+
pfree(cp[0]);
1545+
pfree(cp[1]);
1546+
1547+
switch (ns1->itemtype)
1548+
{
1549+
case PLPGSQL_NSTYPE_REC:
1550+
{
1551+
PLpgSQL_recfieldproperties *new;
1552+
1553+
new = palloc(sizeof(PLpgSQL_recfieldproperties));
1554+
new->dtype = PLPGSQL_DTYPE_RECFIELDNAMES;
1555+
new->recparentno = ns1->itemno;
1556+
new->save_fieldnames = NULL;
1557+
plpgsql_adddatum((PLpgSQL_datum *) new);
1558+
plpgsql_yylval.scalar = (PLpgSQL_datum *) new;
1559+
ret = T_SCALAR; /* ??? */
1560+
break;
1561+
}
1562+
default:
1563+
break;
1564+
}
1565+
return ret;
1566+
}
1567+
1568+
14411569
/*
14421570
* plpgsql_build_variable - build a datum-array entry of a given
14431571
* datatype

0 commit comments

Comments
 (0)