Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit38c7700

Browse files
committed
Add dynamic record inspection to PL/PgSQL, useful for generic triggers:
 tval2 := r.(cname);or columns := r.(*);Titus von Boxberg
1 parent88ba64d commit38c7700

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/3011:58:05 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.93 2006/05/3012: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+
colnameTEXT;
902+
colcontentTEXT;
903+
colnamesTEXT[];
904+
colnINT4;
905+
coliINT4;
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/3011:58:05 momjian Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_comp.c,v 1.105 2006/05/3012: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
returnT_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+
intret=T_ERROR;
1454+
char*fieldvar;
1455+
intfl;
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+
returnT_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+
returnT_ERROR;
1486+
1487+
switch (ns1->itemtype)
1488+
{
1489+
casePLPGSQL_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+
returnret;
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+
intret=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+
returnT_ERROR;
1542+
}
1543+
1544+
pfree(cp[0]);
1545+
pfree(cp[1]);
1546+
1547+
switch (ns1->itemtype)
1548+
{
1549+
casePLPGSQL_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+
returnret;
1566+
}
1567+
1568+
14411569
/*
14421570
* plpgsql_build_variable - build a datum-array entry of a given
14431571
* datatype

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp