From b6477c6295226ae7d7f43185ce0ab55f47bd5f94 Mon Sep 17 00:00:00 2001
From: Bruce Momjian <bruce@momjian.us>
Date: Tue, 30 May 2006 11:54:51 +0000
Subject: Add regexp_replace() to string functions section.

Joachim Wieland
---
 doc/src/sgml/func.sgml    | 23 +++++++++++++++++----
 doc/src/sgml/plpgsql.sgml | 51 ++++++++++++++++++++++++++++++++++++++++++++++-
 2 files changed, 69 insertions(+), 5 deletions(-)

(limited to 'doc/src')

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 0a9a62b759f..5ce49880b53 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.318 2006/05/18 03:18:24 momjian Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.319 2006/05/30 11:54:51 momjian Exp $ -->
 
  <chapter id="functions">
   <title>Functions and Operators</title>
@@ -1112,7 +1112,9 @@
        <entry><literal><function>substring</function>(<parameter>string</parameter> from <replaceable>pattern</replaceable>)</literal></entry>
        <entry><type>text</type></entry>
        <entry>
-        Extract substring matching POSIX regular expression
+        Extract substring matching POSIX regular expression. See
+        <xref linkend="functions-matching"> for more information on pattern
+        matching.
        </entry>
        <entry><literal>substring('Thomas' from '...$')</literal></entry>
        <entry><literal>mas</literal></entry>
@@ -1122,8 +1124,9 @@
        <entry><literal><function>substring</function>(<parameter>string</parameter> from <replaceable>pattern</replaceable> for <replaceable>escape</replaceable>)</literal></entry>
        <entry><type>text</type></entry>
        <entry>
-        Extract substring matching <acronym>SQL</acronym> regular
-        expression
+        Extract substring matching <acronym>SQL</acronym> regular expression.
+        See <xref linkend="functions-matching"> for more information on
+        pattern matching.
        </entry>
        <entry><literal>substring('Thomas' from '%#"o_a#"_' for '#')</literal></entry>
        <entry><literal>oma</literal></entry>
@@ -1420,6 +1423,18 @@
        <entry><literal>'O''Reilly'</literal></entry>
       </row>
 
+      <row>
+       <entry><literal><function>regexp_replace</function>(<parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>, <parameter>replacement</parameter> <type>text</type> [,<parameter>flags</parameter> <type>text</type>])</literal></entry>
+       <entry><type>text</type></entry>
+       <entry>
+        Replace substring matching POSIX regular expression. See
+        <xref linkend="functions-matching"> for more information on pattern
+        matching.
+       </entry>
+       <entry><literal>regexp_replace('Thomas', '.[mN]a.', 'M')</literal></entry>
+       <entry><literal>ThM</literal></entry>
+      </row>
+
       <row>
        <entry><literal><function>repeat</function>(<parameter>string</parameter> <type>text</type>, <parameter>number</parameter> <type>int</type>)</literal></entry>
        <entry><type>text</type></entry>
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 2fc998ee909..ef5e9e3ece0 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.90 2006/05/30 11:40:21 momjian Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.91 2006/05/30 11:54:51 momjian Exp $ -->
 
 <chapter id="plpgsql"> 
   <title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
@@ -879,6 +879,55 @@ SELECT merge_fields(t.*) FROM table1 t WHERE ... ;
     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
+		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>
+
    <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>
-- 
cgit v1.2.3