Neil,

Here it is :)

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!
Index: doc/src/sgml/plperl.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/plperl.sgml,v
retrieving revision 2.22
diff -2 -c -r2.22 plperl.sgml
*** doc/src/sgml/plperl.sgml    14 Dec 2003 00:10:32 -0000      2.22
--- doc/src/sgml/plperl.sgml    27 Apr 2004 21:10:23 -0000
***************
*** 47,53 ****
     To create a function in the PL/Perl language, use the standard syntax:
  <programlisting>
! CREATE FUNCTION <replaceable>funcname</replaceable> 
(<replaceable>argument-types</replaceable>) RETURNS 
<replaceable>return-type</replaceable> AS '
      # PL/Perl function body
! ' LANGUAGE plperl;
  </programlisting>
     The body of the function is ordinary Perl code.
--- 47,54 ----
     To create a function in the PL/Perl language, use the standard syntax:
  <programlisting>
! CREATE FUNCTION <replaceable>funcname</replaceable>
! (<replaceable>argument-types</replaceable>) RETURNS 
<replaceable>return-type</replaceable> AS $dollar_quote$
      # PL/Perl function body
! $dollar_quote$ LANGUAGE plperl;
  </programlisting>
     The body of the function is ordinary Perl code.
***************
*** 66,73 ****
  
  <programlisting>
! CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS '
      if ($_[0] > $_[1]) { return $_[0]; }
      return $_[1];
! ' LANGUAGE plperl;
  </programlisting>
    </para>
--- 67,74 ----
  
  <programlisting>
! CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $perl_max$
      if ($_[0] > $_[1]) { return $_[0]; }
      return $_[1];
! $perl_max$ LANGUAGE plperl;
  </programlisting>
    </para>
***************
*** 89,93 ****
  
  <programlisting>
! CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS '
      my ($a,$b) = @_;
      if (! defined $a) {
--- 90,94 ----
  
  <programlisting>
! CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $perl_max$
      my ($a,$b) = @_;
      if (! defined $a) {
***************
*** 98,102 ****
      if ($a > $b) { return $a; }
      return $b;
! ' LANGUAGE plperl;
  </programlisting>
    </para>
--- 99,103 ----
      if ($a > $b) { return $a; }
      return $b;
! $perl_max$ LANGUAGE plperl;
  </programlisting>
    </para>
***************
*** 120,127 ****
  );
  
! CREATE FUNCTION empcomp(employee) RETURNS integer AS '
      my ($emp) = @_;
!     return $emp->{''basesalary''} + $emp->{''bonus''};
! ' LANGUAGE plperl;
  
  SELECT name, empcomp(employee) FROM employee;
--- 121,128 ----
  );
  
! CREATE FUNCTION empcomp(employee) RETURNS integer AS $empcomp$
      my ($emp) = @_;
!     return $emp->{'basesalary'} + $emp->{'bonus'};
! $empcomp$ LANGUAGE plperl;
  
  SELECT name, empcomp(employee) FROM employee;
***************
*** 137,146 ****
     <para>
      Because the function body is passed as an SQL string literal to
!     <command>CREATE FUNCTION</command>, you have to escape single
!     quotes and backslashes within your Perl source, typically by
!     doubling them as shown in the above example.  Another possible
!     approach is to avoid writing single quotes by using Perl's
!     extended quoting operators (<literal>q[]</literal>,
!     <literal>qq[]</literal>, <literal>qw[]</literal>).
     </para>
    </tip>
--- 138,147 ----
     <para>
      Because the function body is passed as an SQL string literal to
!     <command>CREATE FUNCTION</command>, you have to use dollar quoting
!     or escape single quotes and backslashes within your Perl source,
!     typically by doubling them.  Another possible approach is to avoid
!     writing single quotes by using Perl's extended quoting operators
!     (<literal>q[]</literal>, <literal>qq[]</literal>,
!     <literal>qw[]</literal>).
     </para>
    </tip>
***************
*** 227,235 ****
     system operations are not allowed for security reasons:
  <programlisting>
! CREATE FUNCTION badfunc() RETURNS integer AS '
      open(TEMP, ">/tmp/badfile");
      print TEMP "Gotcha!\n";
      return 1;
! ' LANGUAGE plperl;
  </programlisting>
     The creation of the function will succeed, but executing it will not.
--- 228,236 ----
     system operations are not allowed for security reasons:
  <programlisting>
! CREATE FUNCTION badfunc() RETURNS integer AS $badfunc$
      open(TEMP, ">/tmp/badfile");
      print TEMP "Gotcha!\n";
      return 1;
! $badfunc$ LANGUAGE plperl;
  </programlisting>
     The creation of the function will succeed, but executing it will not.
Index: doc/src/sgml/plpgsql.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/plpgsql.sgml,v
retrieving revision 1.37
diff -2 -c -r1.37 plpgsql.sgml
*** doc/src/sgml/plpgsql.sgml   26 Mar 2004 03:18:28 -0000      1.37
--- doc/src/sgml/plpgsql.sgml   27 Apr 2004 21:10:24 -0000
***************
*** 262,272 ****
     Since the code of a <application>PL/pgSQL</> function is specified in
     <command>CREATE FUNCTION</command> as a string literal, single
!    quotes inside the function body must be escaped by doubling them.
!    This can lead to
!    rather complicated code at times, especially if you are writing a
!    function that generates other functions, as in the example in <xref
!    linkend="plpgsql-statements-executing-dyn">.  This chart may be useful
!    as a summary of the needed numbers of quotation marks in
!    various situations.
    </para>
  
--- 262,276 ----
     Since the code of a <application>PL/pgSQL</> function is specified in
     <command>CREATE FUNCTION</command> as a string literal, single
!    quotes inside the function body must be escaped by doubling them
!     unless the string literal comprising the function body is dollar
!     quoted.
!     </para>
! 
!     <para>
!     Doubling can lead to incomprehensible code at times, especially if
!     you are writing a function that generates other functions, as in the
!     example in <xref linkend="plpgsql-statements-executing-dyn">.  This
!     chart may be useful when translating pre-dollar quoting code into
!     something that is comprehensible.
    </para>
  
***************
*** 419,427 ****
       block is entered, not only once per function call. For example:
  <programlisting>
! CREATE FUNCTION somefunc() RETURNS integer AS '
  DECLARE
      quantity integer := 30;
  BEGIN
!     RAISE NOTICE ''Quantity here is %'', quantity;  -- Quantity here is 30
      quantity := 50;
      --
--- 423,431 ----
       block is entered, not only once per function call. For example:
  <programlisting>
! CREATE FUNCTION somefunc() RETURNS integer AS $func$
  DECLARE
      quantity integer := 30;
  BEGIN
!     RAISE NOTICE 'Quantity here is %', quantity;  -- Quantity here is 30
      quantity := 50;
      --
***************
*** 431,442 ****
          quantity integer := 80;
      BEGIN
!         RAISE NOTICE ''Quantity here is %'', quantity;  -- Quantity here is 80
      END;
  
!     RAISE NOTICE ''Quantity here is %'', quantity;  -- Quantity here is 50
  
      RETURN quantity;
  END;
! ' LANGUAGE plpgsql;
  </programlisting>
      </para>
--- 435,446 ----
          quantity integer := 80;
      BEGIN
!         RAISE NOTICE 'Quantity here is %', quantity;  -- Quantity here is 80
      END;
  
!     RAISE NOTICE 'Quantity here is %', quantity;  -- Quantity here is 50
  
      RETURN quantity;
  END;
! $func$ LANGUAGE plpgsql;
  </programlisting>
      </para>
***************
*** 449,453 ****
       are always executed within a transaction established by an outer query
       --- they cannot start or commit transactions, since
!      <productname>PostgreSQL</productname> does not have nested transactions.
      </para>
    </sect1>
--- 453,457 ----
       are always executed within a transaction established by an outer query
       --- they cannot start or commit transactions, since
!      <productname>PostgreSQL</productname> does not yet have nested transactions.
      </para>
    </sect1>
***************
*** 511,515 ****
  <programlisting>
  quantity integer DEFAULT 32;
! url varchar := ''http://mysite.com'';
  user_id CONSTANT integer := 10;
  </programlisting>
--- 515,519 ----
  <programlisting>
  quantity integer DEFAULT 32;
! url varchar := 'http://mysite.com';
  user_id CONSTANT integer := 10;
  </programlisting>
***************
*** 789,800 ****
  
  <programlisting>
! CREATE FUNCTION logfunc1(text) RETURNS timestamp AS '
      DECLARE
          logtxt ALIAS FOR $1;
      BEGIN
!         INSERT INTO logtable VALUES (logtxt, ''now'');
!         RETURN ''now'';
      END;
! ' LANGUAGE plpgsql;
  </programlisting>
  
--- 793,804 ----
  
  <programlisting>
! CREATE FUNCTION logfunc1(text) RETURNS timestamp AS $f$
      DECLARE
          logtxt ALIAS FOR $1;
      BEGIN
!         INSERT INTO logtable VALUES (logtxt, 'now');
!         RETURN 'now';
      END;
! $f$ LANGUAGE plpgsql;
  </programlisting>
  
***************
*** 802,815 ****
  
  <programlisting>
! CREATE FUNCTION logfunc2(text) RETURNS timestamp AS '
      DECLARE
          logtxt ALIAS FOR $1;
          curtime timestamp;
      BEGIN
!         curtime := ''now'';
          INSERT INTO logtable VALUES (logtxt, curtime);
          RETURN curtime;
      END;
! ' LANGUAGE plpgsql;
  </programlisting>
      </para>
--- 806,819 ----
  
  <programlisting>
! CREATE FUNCTION logfunc2(text) RETURNS timestamp AS $lf2$
      DECLARE
          logtxt ALIAS FOR $1;
          curtime timestamp;
      BEGIN
!         curtime := 'now';
          INSERT INTO logtable VALUES (logtxt, curtime);
          RETURN curtime;
      END;
! $lf2$ LANGUAGE plpgsql;
  </programlisting>
      </para>
***************
*** 871,875 ****
      listed here.
     </para>
!    
     <sect2 id="plpgsql-statements-assignment">
      <title>Assignment</title>
--- 875,879 ----
      listed here.
     </para>
! 
     <sect2 id="plpgsql-statements-assignment">
      <title>Assignment</title>
***************
*** 969,977 ****
       INTO</command> statement to determine whether the assignment was successful
       (that is, at least one row was was returned by the query). For example:
!   
  <programlisting>
  SELECT INTO myrec * FROM emp WHERE empname = myname;
  IF NOT FOUND THEN
!     RAISE EXCEPTION ''employee % not found'', myname;
  END IF;
  </programlisting>
--- 973,981 ----
       INTO</command> statement to determine whether the assignment was successful
       (that is, at least one row was was returned by the query). For example:
! 
  <programlisting>
  SELECT INTO myrec * FROM emp WHERE empname = myname;
  IF NOT FOUND THEN
!     RAISE EXCEPTION 'employee % not found', myname;
  END IF;
  </programlisting>
***************
*** 979,982 ****
--- 983,991 ----
  
      <para>
+     You <emphasis>were</emphasis> using dollar quoting to make the
+     function body, weren't you?
+     </para>
+ 
+     <para>
       To test for whether a record/row result is null, you can use the
       <literal>IS NULL</literal> conditional.  There is, however, no
***************
*** 992,996 ****
      IF users_rec.homepage IS NULL THEN
          -- user entered no homepage, return "http://";
!         RETURN ''http://'';
      END IF;
  END;
--- 1001,1005 ----
      IF users_rec.homepage IS NULL THEN
          -- user entered no homepage, return "http://";
!         RETURN 'http://';
      END IF;
  END;
***************
*** 1033,1044 ****
       An example:
  <programlisting>
! PERFORM create_mv(''cs_session_page_requests_mv'', my_query);
  </programlisting>
      </para>
     </sect2>
!    
     <sect2 id="plpgsql-statements-executing-dyn">
      <title>Executing Dynamic Commands</title>
!     
      <para>
       Oftentimes you will want to generate dynamic commands inside your
--- 1042,1053 ----
       An example:
  <programlisting>
! PERFORM create_mv('cs_session_page_requests_mv', my_query);
  </programlisting>
      </para>
     </sect2>
! 
     <sect2 id="plpgsql-statements-executing-dyn">
      <title>Executing Dynamic Commands</title>
! 
      <para>
       Oftentimes you will want to generate dynamic commands inside your
***************
*** 1067,1076 ****
  
      <para>
!     When working with dynamic commands you will have to face
!     escaping of single quotes in <application>PL/pgSQL</>. Please refer to the
!     overview in <xref linkend="plpgsql-quote-tips">,
!     which can save you some effort.
      </para>
!      
      <para>
       Unlike all other commands in <application>PL/pgSQL</>, a command
--- 1076,1087 ----
  
      <para>
!     When working with dynamic commands you will have to face escaping
!     of single quotes in <application>PL/pgSQL</>.  The recommended method
!     is dollar quoting.  If you have legacy code which does
!     <emphasis>not</emphasis> use dollar quoting, please refer to the
!     overview in <xref linkend="plpgsql-quote-tips">, which can save you
!     some effort when translating said code to a more reasonable scheme.
      </para>
! 
      <para>
       Unlike all other commands in <application>PL/pgSQL</>, a command
***************
*** 1081,1085 ****
       actions on variable tables and columns.
      </para>
!   
      <para>
       The results from <command>SELECT</command> commands are discarded
--- 1092,1096 ----
       actions on variable tables and columns.
      </para>
! 
      <para>
       The results from <command>SELECT</command> commands are discarded
***************
*** 1094,1104 ****
  
      <para>
!      An example:
  <programlisting>
! EXECUTE ''UPDATE tbl SET ''
          || quote_ident(colname)
!         || '' = ''
          || quote_literal(newvalue)
!         || '' WHERE ...'';
  </programlisting>
      </para>
--- 1105,1117 ----
  
      <para>
!      An example (except where noted, all examples herein assume that
!     you have dollar quoting and are using it):
! 
  <programlisting>
! EXECUTE 'UPDATE tbl SET '
          || quote_ident(colname)
!         || ' = '
          || quote_literal(newvalue)
!         || ' WHERE ...';
  </programlisting>
      </para>
***************
*** 1145,1155 ****
                   || referrer_keys.referrer_type || ''''''; END IF;''; 
      END LOOP; 
!   
      a_output := a_output || '' RETURN NULL; END; '''' LANGUAGE plpgsql;''; 
!  
      EXECUTE a_output; 
  END; 
  ' LANGUAGE plpgsql;
  </programlisting>
      </para>
     </sect2>
--- 1158,1201 ----
                   || referrer_keys.referrer_type || ''''''; END IF;''; 
      END LOOP; 
! 
      a_output := a_output || '' RETURN NULL; END; '''' LANGUAGE plpgsql;''; 
! 
      EXECUTE a_output; 
  END; 
  ' LANGUAGE plpgsql;
  </programlisting>
+ 
+ And here is an equivalent using dollar quoting.  At least it is more
+ legible than the above, although both versions show that the design,
+ rather than merely the formatting, needs to be re-thought entire.
+ 
+ <programlisting>
+ CREATE or replace FUNCTION cs_update_referrer_type_proc2() RETURNS integer AS $func$
+  DECLARE
+      referrer_keys RECORD;  -- declare a generic record to be used in a FOR
+      a_output varchar(4000);
+  BEGIN 
+      a_output := 'CREATE FUNCTION cs_find_referrer_type(varchar, varchar, varchar) 
+                    RETURNS varchar AS $innerfunc$ 
+                       DECLARE 
+                           v_host ALIAS FOR $1; 
+                           v_domain ALIAS FOR $2; 
+                           v_url ALIAS FOR $3;
+                       BEGIN '; 
+ 
+      -- Notice how we scan through the results of a query in a FOR loop
+      -- using the FOR &lt;record&gt; construct.
+ 
+      FOR referrer_keys IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP
+          a_output := a_output || ' IF v_' || referrer_keys.kind || ' LIKE $$' 
+                   || referrer_keys.key_string || '$$ THEN RETURN $$' 
+                   || referrer_keys.referrer_type || '$$; END IF;'; 
+      END LOOP; 
+ 
+      a_output := a_output || ' RETURN NULL; END; $innerfunc$ LANGUAGE plpgsql;'; 
+      EXECUTE a_output; 
+ END; 
+ $func$ LANGUAGE plpgsql;
+ </programlisting>
      </para>
     </sect2>
***************
*** 1253,1257 ****
      flexible and powerful way. 
     </para>
!    
     <sect2 id="plpgsql-statements-returning">
      <title>Returning From a Function</title>
--- 1299,1303 ----
      flexible and powerful way. 
     </para>
! 
     <sect2 id="plpgsql-statements-returning">
      <title>Returning From a Function</title>
***************
*** 1363,1367 ****
      </sect3>
     </sect2>
!     
     <sect2 id="plpgsql-conditionals">
      <title>Conditionals</title>
--- 1409,1413 ----
      </sect3>
     </sect2>
! 
     <sect2 id="plpgsql-conditionals">
      <title>Conditionals</title>
***************
*** 1435,1443 ****
          Examples:
  <programlisting>
! IF parentid IS NULL OR parentid = ''''
  THEN
      RETURN fullname;
  ELSE
!     RETURN hp_true_filename(parentid) || ''/'' || fullname;
  END IF;
  </programlisting>
--- 1481,1489 ----
          Examples:
  <programlisting>
! IF parentid IS NULL OR parentid = ''
  THEN
      RETURN fullname;
  ELSE
!     RETURN hp_true_filename(parentid) || '/' || fullname;
  END IF;
  </programlisting>
***************
*** 1446,1452 ****
  IF v_count > 0 THEN 
      INSERT INTO users_count (count) VALUES (v_count);
!     RETURN ''t'';
  ELSE
!     RETURN ''f'';
  END IF;
  </programlisting>
--- 1492,1498 ----
  IF v_count > 0 THEN 
      INSERT INTO users_count (count) VALUES (v_count);
!     RETURN 't';
  ELSE
!     RETURN 'f';
  END IF;
  </programlisting>
***************
*** 1462,1470 ****
  
  <programlisting>
! IF demo_row.sex = ''m'' THEN
!     pretty_sex := ''man'';
  ELSE
!     IF demo_row.sex = ''f'' THEN
!         pretty_sex := ''woman'';
      END IF;
  END IF;
--- 1508,1516 ----
  
  <programlisting>
! IF demo_row.sex = 'm' THEN
!     pretty_sex := 'man';
  ELSE
!     IF demo_row.sex = 'f' THEN
!         pretty_sex := 'woman';
      END IF;
  END IF;
***************
*** 1515,1526 ****
  <programlisting>
  IF number = 0 THEN
!     result := ''zero'';
  ELSIF number &gt; 0 THEN 
!     result := ''positive'';
  ELSIF number &lt; 0 THEN
!     result := ''negative'';
  ELSE
      -- hmm, the only other possibility is that number is null
!     result := ''NULL'';
  END IF;
  </programlisting>
--- 1561,1572 ----
  <programlisting>
  IF number = 0 THEN
!     result := 'zero';
  ELSIF number &gt; 0 THEN 
!     result := 'positive';
  ELSIF number &lt; 0 THEN
!     result := 'negative';
  ELSE
      -- hmm, the only other possibility is that number is null
!     result := 'NULL';
  END IF;
  </programlisting>
***************
*** 1667,1671 ****
  FOR i IN 1..10 LOOP
      -- some computations here
!     RAISE NOTICE ''i is %'', i;
  END LOOP;
  
--- 1713,1717 ----
  FOR i IN 1..10 LOOP
      -- some computations here
!     RAISE NOTICE 'i is %', i;
  END LOOP;
  
***************
*** 1705,1709 ****
      mviews RECORD;
  BEGIN
!     PERFORM cs_log(''Refreshing materialized views...'');
  
      FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP
--- 1751,1755 ----
      mviews RECORD;
  BEGIN
!     PERFORM cs_log('Refreshing materialized views...');
  
      FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP
***************
*** 1711,1720 ****
          -- Now "mviews" has one record from cs_materialized_views
  
!         PERFORM cs_log(''Refreshing materialized view '' || 
quote_ident(mviews.mv_name) || ''...'');
!         EXECUTE ''TRUNCATE TABLE  '' || quote_ident(mviews.mv_name);
!         EXECUTE ''INSERT INTO '' || quote_ident(mviews.mv_name) || '' '' || 
mviews.mv_query;
      END LOOP;
  
!     PERFORM cs_log(''Done refreshing materialized views.'');
      RETURN 1;
  END;
--- 1757,1766 ----
          -- Now "mviews" has one record from cs_materialized_views
  
!         PERFORM cs_log('Refreshing materialized view ' || 
quote_ident(mviews.mv_name) || '...');
!         EXECUTE 'TRUNCATE TABLE  ' || quote_ident(mviews.mv_name);
!         EXECUTE 'INSERT INTO ' || quote_ident(mviews.mv_name) || ' ' || 
mviews.mv_query;
      END LOOP;
  
!     PERFORM cs_log('Done refreshing materialized views.');
      RETURN 1;
  END;
***************
*** 1779,1783 ****
      large row sets from functions.
     </para>
!    
     <sect2 id="plpgsql-cursor-declarations">
      <title>Declaring Cursor Variables</title>
--- 1825,1829 ----
      large row sets from functions.
     </para>
! 
     <sect2 id="plpgsql-cursor-declarations">
      <title>Declaring Cursor Variables</title>
***************
*** 1878,1882 ****
          An example:
  <programlisting>
! OPEN curs1 FOR EXECUTE ''SELECT * FROM '' || quote_ident($1);
  </programlisting>
         </para>
--- 1924,1928 ----
          An example:
  <programlisting>
! OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
  </programlisting>
         </para>
***************
*** 1979,1983 ****
         </para>
       </sect3>
!  
      <sect3>
       <title>Returning Cursors</title>
--- 2025,2029 ----
         </para>
       </sect3>
! 
      <sect3>
       <title>Returning Cursors</title>
***************
*** 2041,2045 ****
         <para>
          The following example uses automatic cursor name generation:
!          
  <programlisting>
  CREATE FUNCTION reffunc2() RETURNS refcursor AS '
--- 2087,2091 ----
         <para>
          The following example uses automatic cursor name generation:
! 
  <programlisting>
  CREATE FUNCTION reffunc2() RETURNS refcursor AS '
***************
*** 2054,2058 ****
  BEGIN;
  SELECT reffunc2();
!   
        reffunc2      
  --------------------
--- 2100,2104 ----
  BEGIN;
  SELECT reffunc2();
! 
        reffunc2      
  --------------------
***************
*** 2104,2108 ****
     <!--
     This example should work, but does not:
!         RAISE NOTICE ''Id number '' || key || '' not found!'';
     Put it back when we allow non-string-literal formats.
      -->
--- 2150,2154 ----
     <!--
     This example should work, but does not:
!         RAISE NOTICE 'Id number ' || key || ' not found!';
     Put it back when we allow non-string-literal formats.
      -->
***************
*** 2112,2116 ****
      <literal>%</literal> in the string:
  <programlisting>
! RAISE NOTICE ''Calling cs_create_job(%)'', v_job_id;
  </programlisting>
     </para>
--- 2158,2162 ----
      <literal>%</literal> in the string:
  <programlisting>
! RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;
  </programlisting>
     </para>
***************
*** 2119,2123 ****
      This example will abort the transaction with the given error message:
  <programlisting>
! RAISE EXCEPTION ''Inexistent ID --> %'', user_id;
  </programlisting>
     </para>
--- 2165,2169 ----
      This example will abort the transaction with the given error message:
  <programlisting>
! RAISE EXCEPTION 'Inexistent ID --> %', user_id;
  </programlisting>
     </para>
***************
*** 2172,2176 ****
     trigger, several special variables are created automatically in the 
     top-level block. They are:
!    
     <variablelist>
      <varlistentry>
--- 2218,2222 ----
     trigger, several special variables are created automatically in the 
     top-level block. They are:
! 
     <variablelist>
      <varlistentry>
***************
*** 2335,2359 ****
  );
  
! CREATE FUNCTION emp_stamp() RETURNS trigger AS '
      BEGIN
          -- Check that empname and salary are given
          IF NEW.empname IS NULL THEN
!             RAISE EXCEPTION ''empname cannot be null'';
          END IF;
          IF NEW.salary IS NULL THEN
!             RAISE EXCEPTION ''% cannot have null salary'', NEW.empname;
          END IF;
  
          -- Who works for us when she must pay for it?
          IF NEW.salary &lt; 0 THEN
!             RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname;
          END IF;
  
          -- Remember who changed the payroll when
!         NEW.last_date := ''now'';
          NEW.last_user := current_user;
          RETURN NEW;
      END;
! ' LANGUAGE plpgsql;
  
  CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
--- 2381,2405 ----
  );
  
! CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
      BEGIN
          -- Check that empname and salary are given
          IF NEW.empname IS NULL THEN
!             RAISE EXCEPTION 'empname cannot be null';
          END IF;
          IF NEW.salary IS NULL THEN
!             RAISE EXCEPTION '% cannot have null salary', NEW.empname;
          END IF;
  
          -- Who works for us when she must pay for it?
          IF NEW.salary &lt; 0 THEN
!             RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
          END IF;
  
          -- Remember who changed the payroll when
!         NEW.last_date := 'now';
          NEW.last_user := current_user;
          RETURN NEW;
      END;
! $emp_stamp$ LANGUAGE plpgsql;
  
  CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
***************
*** 2452,2456 ****
          RETURN v_name;
      END IF;
!     RETURN v_name || '/' || v_version;
  END;
  /
--- 2498,2502 ----
          RETURN v_name;
      END IF;
!     RETURN v_name || ''/'' || v_version;
  END;
  /
***************
*** 2515,2519 ****
  <programlisting>
  CREATE OR REPLACE FUNCTION cs_fmt_browser_version(varchar, varchar)
! RETURNS varchar AS '
  DECLARE
      v_name ALIAS FOR $1;
--- 2561,2565 ----
  <programlisting>
  CREATE OR REPLACE FUNCTION cs_fmt_browser_version(varchar, varchar)
! RETURNS varchar AS $cs_fmt_browser_version$
  DECLARE
      v_name ALIAS FOR $1;
***************
*** 2523,2529 ****
          return v_name;
      END IF;
!     RETURN v_name || ''/'' || v_version;
  END;
! ' LANGUAGE plpgsql;
  </programlisting>
      </para>
--- 2569,2575 ----
          return v_name;
      END IF;
!     RETURN v_name || '/' || v_version;
  END;
! $cs_fmt_browser_version$ LANGUAGE plpgsql;
  </programlisting>
      </para>
***************
*** 2535,2539 ****
      ensuing quoting problems.
     </para>
!  
     <example id="plpgsql-porting-ex2">
      <title>Porting a Function that Creates Another Function from 
<application>PL/SQL</> to <application>PL/pgSQL</></title>
--- 2581,2585 ----
      ensuing quoting problems.
     </para>
! 
     <example id="plpgsql-porting-ex2">
      <title>Porting a Function that Creates Another Function from 
<application>PL/SQL</> to <application>PL/pgSQL</></title>
***************
*** 2578,2615 ****
  
  <programlisting>
! CREATE FUNCTION cs_update_referrer_type_proc() RETURNS integer AS '
  DECLARE
!     referrer_keys RECORD;  -- Declare a generic record to be used in a FOR
!     a_output varchar(4000);
  BEGIN 
!     a_output := ''CREATE FUNCTION cs_find_referrer_type(varchar, varchar, varchar) 
!                   RETURNS varchar AS '''' 
                       DECLARE 
                           v_host ALIAS FOR $1; 
                           v_domain ALIAS FOR $2; 
                           v_url ALIAS FOR $3;
!                      BEGIN ''; 
  
!     -- Notice how we scan through the results of a query in a FOR loop
!     -- using the FOR &lt;record&gt; construct.
  
!     FOR referrer_keys IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP
!         a_output := a_output || '' IF v_'' || referrer_keys.kind || '' LIKE 
'''''''''' 
!                  || referrer_keys.key_string || '''''''''' THEN RETURN '''''' 
!                  || referrer_keys.referrer_type || ''''''; END IF;''; 
!     END LOOP; 
!   
!     a_output := a_output || '' RETURN NULL; END; '''' LANGUAGE plpgsql;''; 
!  
!     -- EXECUTE will work because we are not substituting any variables.
!     -- Otherwise it would fail.  Look at PERFORM for another way to run functions.
!     
!     EXECUTE a_output; 
  END; 
! ' LANGUAGE plpgsql;
  </programlisting>
      </para>
     </example>
!  
     <para>
      <xref linkend="plpgsql-porting-ex3"> shows how to port a function
--- 2624,2659 ----
  
  <programlisting>
! CREATE or replace FUNCTION cs_update_referrer_type_proc2() RETURNS
! text AS $func$
  DECLARE
!     referrer_keys RECORD;  -- declare a generic record to be used in a FOR
!     a_output TEXT;
  BEGIN 
!     a_output := 'CREATE FUNCTION cs_find_referrer_type(varchar, varchar, varchar) 
!                   RETURNS varchar AS $innerfunc$ 
                       DECLARE 
                           v_host ALIAS FOR $1; 
                           v_domain ALIAS FOR $2; 
                           v_url ALIAS FOR $3;
!                      BEGIN '; 
  
!      -- Notice how we scan through the results of a query in a FOR loop
!      -- using the FOR &lt;record&gt; construct.
  
!      FOR referrer_keys IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP
!          a_output := a_output || ' IF v_' || referrer_keys.kind || ' LIKE $$' 
!                   || referrer_keys.key_string || '$$ THEN RETURN $$' 
!                   || referrer_keys.referrer_type || '$$; END IF;'; 
!      END LOOP; 
! 
!      a_output := a_output || ' RETURN NULL; END; $innerfunc$ LANGUAGE plpgsql;'; 
! 
!      return a_output; 
  END; 
! $func$ LANGUAGE plpgsql;
  </programlisting>
      </para>
     </example>
! 
     <para>
      <xref linkend="plpgsql-porting-ex3"> shows how to port a function
***************
*** 2654,2671 ****
      v_path := NULL;
      v_query := NULL;
!     a_pos1 := instr(v_url, '//');
  
      IF a_pos1 = 0 THEN
          RETURN;
      END IF;
!     a_pos2 := instr(v_url, '/', a_pos1 + 2);
      IF a_pos2 = 0 THEN
          v_host := substr(v_url, a_pos1 + 2);
!         v_path := '/';
          RETURN;
      END IF;
  
      v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
!     a_pos1 := instr(v_url, '?', a_pos2 + 1);
  
      IF a_pos1 = 0 THEN
--- 2698,2715 ----
      v_path := NULL;
      v_query := NULL;
!     a_pos1 := instr(v_url, ''//'');
  
      IF a_pos1 = 0 THEN
          RETURN;
      END IF;
!     a_pos2 := instr(v_url, ''/'', a_pos1 + 2);
      IF a_pos2 = 0 THEN
          v_host := substr(v_url, a_pos1 + 2);
!         v_path := ''/'';
          RETURN;
      END IF;
  
      v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
!     a_pos1 := instr(v_url, ''?'', a_pos2 + 1);
  
      IF a_pos1 = 0 THEN
***************
*** 2687,2691 ****
  
  <programlisting>
! CREATE OR REPLACE FUNCTION cs_parse_url_host(varchar) RETURNS varchar AS '
  DECLARE
      v_url ALIAS FOR $1;
--- 2731,2735 ----
  
  <programlisting>
! CREATE OR REPLACE FUNCTION cs_parse_url_host(varchar) RETURNS varchar AS $func$
  DECLARE
      v_url ALIAS FOR $1;
***************
*** 2697,2710 ****
  BEGIN 
      v_host := NULL; 
!     a_pos1 := instr(v_url, ''//'');
  
      IF a_pos1 = 0 THEN 
!         RETURN '''';  -- Return a blank
      END IF; 
  
!     a_pos2 := instr(v_url,''/'',a_pos1 + 2);
      IF a_pos2 = 0 THEN 
          v_host := substr(v_url, a_pos1 + 2); 
!         v_path := ''/''; 
          RETURN v_host; 
      END IF; 
--- 2741,2754 ----
  BEGIN 
      v_host := NULL; 
!     a_pos1 := instr(v_url, '//');
  
      IF a_pos1 = 0 THEN 
!         RETURN '';  -- Return a blank
      END IF; 
  
!     a_pos2 := instr(v_url,'/',a_pos1 + 2);
      IF a_pos2 = 0 THEN 
          v_host := substr(v_url, a_pos1 + 2); 
!         v_path := '/'; 
          RETURN v_host; 
      END IF; 
***************
*** 2713,2717 ****
      RETURN v_host; 
  END; 
! ' LANGUAGE plpgsql;
  </programlisting>
      </para>
--- 2757,2761 ----
      RETURN v_host; 
  END; 
! $func$ LANGUAGE plpgsql;
  </programlisting>
      </para>
***************
*** 2798,2802 ****
  
  <programlisting>
! CREATE OR REPLACE FUNCTION cs_create_job(integer) RETURNS integer AS '
  DECLARE
      v_job_id ALIAS FOR $1;
--- 2842,2846 ----
  
  <programlisting>
! CREATE OR REPLACE FUNCTION cs_create_job(integer) RETURNS integer AS $func$
  DECLARE
      v_job_id ALIAS FOR $1;
***************
*** 2809,2813 ****
      IF a_running_job_count > 0
      THEN
!         RAISE EXCEPTION ''Unable to create a new job: a job is currently running.'';
      END IF;
  
--- 2853,2857 ----
      IF a_running_job_count > 0
      THEN
!         RAISE EXCEPTION 'Unable to create a new job: a job is currently running.';
      END IF;
  
***************
*** 2821,2830 ****
          RETURN 1;
      ELSE
!         RAISE NOTICE ''Job already running.'';<co id="co.plpgsql-porting-raise">
      END IF;
  
      RETURN 0;
  END;
! ' LANGUAGE plpgsql;
  </programlisting>
  
--- 2865,2874 ----
          RETURN 1;
      ELSE
!         RAISE NOTICE 'Job already running.';<co id="co.plpgsql-porting-raise">
      END IF;
  
      RETURN 0;
  END;
! $func$ LANGUAGE plpgsql;
  </programlisting>
  
***************
*** 2859,2863 ****
       <function>quote_string(text)</function> as described in <xref
       linkend="plpgsql-statements-executing-dyn">.  Constructs of the
!      type <literal>EXECUTE ''SELECT * FROM $1'';</literal> will not
       work unless you use these functions.
      </para>
--- 2903,2907 ----
       <function>quote_string(text)</function> as described in <xref
       linkend="plpgsql-statements-executing-dyn">.  Constructs of the
!      type <literal>EXECUTE 'SELECT * FROM $1';</literal> will not
       work unless you use these functions.
      </para>
***************
*** 2882,2888 ****
  
  <programlisting>
! CREATE FUNCTION foo(...) RETURNS integer AS '
  ...
! ' LANGUAGE plpgsql STRICT IMMUTABLE;
  </programlisting>
      </para>
--- 2926,2932 ----
  
  <programlisting>
! CREATE FUNCTION foo(...) RETURNS integer AS $f$
  ...
! $f$ LANGUAGE plpgsql STRICT IMMUTABLE;
  </programlisting>
      </para>
***************
*** 2909,2913 ****
  --
  
! CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS '
  DECLARE
      pos integer;
--- 2953,2957 ----
  --
  
! CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $f$
  DECLARE
      pos integer;
***************
*** 2916,2923 ****
      RETURN pos;
  END;
! ' LANGUAGE plpgsql;
  
  
! CREATE FUNCTION instr(varchar, varchar, varchar) RETURNS integer AS '
  DECLARE
      string ALIAS FOR $1;
--- 2960,2967 ----
      RETURN pos;
  END;
! $f$ LANGUAGE plpgsql;
  
  
! CREATE FUNCTION instr(varchar, varchar, varchar) RETURNS integer AS $f$
  DECLARE
      string ALIAS FOR $1;
***************
*** 2958,2965 ****
      END IF;
  END;
! ' LANGUAGE plpgsql;
  
  
! CREATE FUNCTION instr(varchar, varchar, integer, integer) RETURNS integer AS '
  DECLARE
      string ALIAS FOR $1;
--- 3002,3009 ----
      END IF;
  END;
! $f$ LANGUAGE plpgsql;
  
  
! CREATE FUNCTION instr(varchar, varchar, integer, integer) RETURNS integer AS $f$
  DECLARE
      string ALIAS FOR $1;
***************
*** 3019,3026 ****
      END IF;
  END;
! ' LANGUAGE plpgsql;
  </programlisting>
    </sect2>
!   
   </sect1>
  
--- 3063,3070 ----
      END IF;
  END;
! $f$ LANGUAGE plpgsql;
  </programlisting>
    </sect2>
! 
   </sect1>
  
Index: doc/src/sgml/pltcl.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/pltcl.sgml,v
retrieving revision 2.29
diff -2 -c -r2.29 pltcl.sgml
*** doc/src/sgml/pltcl.sgml     24 Jan 2004 23:06:29 -0000      2.29
--- doc/src/sgml/pltcl.sgml     27 Apr 2004 21:10:24 -0000
***************
*** 78,84 ****
  
  <programlisting>
! CREATE FUNCTION <replaceable>funcname</replaceable> 
(<replaceable>argument-types</replaceable>) RETURNS 
<replaceable>return-type</replaceable> AS '
      # PL/Tcl function body
! ' LANGUAGE pltcl;
  </programlisting>
  
--- 78,85 ----
  
  <programlisting>
! CREATE FUNCTION <replaceable>funcname</replaceable>
! (<replaceable>argument-types</replaceable>) RETURNS 
<replaceable>return-type</replaceable> AS $tcl$
      # PL/Tcl function body
! $tcl$ LANGUAGE pltcl;
  </programlisting>
  
***************
*** 101,108 ****
  
  <programlisting>
! CREATE FUNCTION tcl_max(integer, integer) RETURNS integer AS '
      if {$1 > $2} {return $1}
      return $2
! ' LANGUAGE pltcl STRICT;
  </programlisting>
  
--- 102,109 ----
  
  <programlisting>
! CREATE FUNCTION tcl_max(integer, integer) RETURNS integer AS $tcl$
      if {$1 > $2} {return $1}
      return $2
! $tcl$ LANGUAGE pltcl STRICT;
  </programlisting>
  
***************
*** 123,127 ****
  
  <programlisting>
! CREATE FUNCTION tcl_max(integer, integer) RETURNS integer AS '
      if {[argisnull 1]} {
          if {[argisnull 2]} { return_null }
--- 124,128 ----
  
  <programlisting>
! CREATE FUNCTION tcl_max(integer, integer) RETURNS integer AS $tcl$
      if {[argisnull 1]} {
          if {[argisnull 2]} { return_null }
***************
*** 131,135 ****
      if {$1 > $2} {return $1}
      return $2
! ' LANGUAGE pltcl;
  </programlisting>
      </para>
--- 132,136 ----
      if {$1 > $2} {return $1}
      return $2
! $tcl$ LANGUAGE pltcl;
  </programlisting>
      </para>
***************
*** 155,159 ****
  );
  
! CREATE FUNCTION overpaid(employee) RETURNS boolean AS '
      if {200000.0 < $1(salary)} {
          return "t"
--- 156,160 ----
  );
  
! CREATE FUNCTION overpaid(employee) RETURNS boolean AS $overpaid$
      if {200000.0 < $1(salary)} {
          return "t"
***************
*** 163,167 ****
      }
      return "f"
! ' LANGUAGE pltcl;
  </programlisting>
      </para>
--- 164,168 ----
      }
      return "f"
! $overpaid$ LANGUAGE pltcl;
  </programlisting>
      </para>
***************
*** 360,382 ****
  
  <programlisting>
! CREATE FUNCTION t1_count(integer, integer) RETURNS integer AS '
      if {![ info exists GD(plan) ]} {
          # prepare the saved plan on the first call
!         set GD(plan) [ spi_prepare \\
!                 "SELECT count(*) AS cnt FROM t1 WHERE num &gt;= \\$1 AND num &lt;= 
\\$2" \\
                  [ list int4 int4 ] ]
      }
      spi_execp -count 1 $GD(plan) [ list $1 $2 ]
      return $cnt
! ' LANGUAGE pltcl;
  </programlisting>
  
!       Note that each backslash that Tcl should see must be doubled when
!       we type in the function, since the main parser processes
!       backslashes, too, in <command>CREATE FUNCTION</>.  We need backslashes inside
!       the query string given to <function>spi_prepare</> to ensure that
!       the <literal>$<replaceable>n</replaceable></> markers will be passed through to
!       <function>spi_prepare</> as-is, and not
!       replaced by Tcl variable substitution.
         </para>
        </listitem>
--- 361,382 ----
  
  <programlisting>
! CREATE FUNCTION t1_count(integer, integer) RETURNS integer AS $tcl$
      if {![ info exists GD(plan) ]} {
          # prepare the saved plan on the first call
!         set GD(plan) [ spi_prepare \
!                 "SELECT count(*) AS cnt FROM t1 WHERE num &gt;= \$1 AND num &lt;= 
\$2" \
                  [ list int4 int4 ] ]
      }
      spi_execp -count 1 $GD(plan) [ list $1 $2 ]
      return $cnt
! $tcl$ LANGUAGE pltcl;
  </programlisting>
  
!     We need backslashes inside the query string given to
!     <function>spi_prepare</> to ensure that the
!     <literal>$<replaceable>n</replaceable></> markers will be passed
!     through to <function>spi_prepare</> as-is, and not replaced by Tcl
!     variable substitution.
! 
         </para>
        </listitem>
***************
*** 426,430 ****
  
  <programlisting>
! SELECT 'doesn''t' AS ret
  </programlisting>
  
--- 426,430 ----
  
  <programlisting>
! SELECT $q$doesn't$q$ AS ret
  </programlisting>
  
***************
*** 612,616 ****
  
  <programlisting>
! CREATE FUNCTION trigfunc_modcount() RETURNS trigger AS '
      switch $TG_op {
          INSERT {
--- 612,616 ----
  
  <programlisting>
! CREATE FUNCTION trigfunc_modcount() RETURNS trigger AS $t$
      switch $TG_op {
          INSERT {
***************
*** 626,630 ****
      }
      return [array get NEW]
! ' LANGUAGE pltcl;
  
  CREATE TABLE mytab (num integer, description text, modcnt integer);
--- 626,630 ----
      }
      return [array get NEW]
! $t$ LANGUAGE pltcl;
  
  CREATE TABLE mytab (num integer, description text, modcnt integer);
Index: doc/src/sgml/syntax.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/syntax.sgml,v
retrieving revision 1.90
diff -2 -c -r1.90 syntax.sgml
*** doc/src/sgml/syntax.sgml    12 Mar 2004 00:25:40 -0000      1.90
--- doc/src/sgml/syntax.sgml    27 Apr 2004 21:10:24 -0000
***************
*** 241,252 ****
        <secondary>escaping</secondary>
       </indexterm>
!      A string constant in SQL is an arbitrary sequence of characters
!      bounded by single quotes (<literal>'</literal>), e.g., <literal>'This
!      is a string'</literal>.  SQL allows single quotes to be embedded
!      in strings by typing two adjacent single quotes, e.g.,
!      <literal>'Dianne''s horse'</literal>.  In
!      <productname>PostgreSQL</productname> single quotes may
!      alternatively be escaped with a backslash (<literal>\</literal>),
!      e.g., <literal>'Dianne\'s horse'</literal>.
      </para>
  
--- 241,284 ----
        <secondary>escaping</secondary>
       </indexterm>
!     A string constant in SQL is an arbitrary sequence of characters
!     bounded by single quotes (<literal>'</literal>), e.g., <literal>'This
!     is a string'</literal>, or dollar quotes, e.g. <literal>$q$This is a
!     string$q$</literal>.  SQL allows single quotes to be embedded in
!     strings by typing two adjacent single quotes, e.g.,
!     <literal>'Dianne''s horse'</literal>.  In
!     <productname>PostgreSQL</productname> single quotes may alternatively
!     be escaped with a backslash (<literal>\</literal>), e.g.,
!     <literal>'Dianne\'s horse'</literal>.  With dollar quotes, this
!     could be written as <literal>$$Dianne's horse$$</literal> or
!     <literal>$long_dollar_quote_string$Dianne's
!     horse$long_dollar_quote_string$</literal>.  Here is an example of
!     how dollar quotes can nest:
!     </para>
! 
! <programlisting>
! CREATE OR REPLACE FUNCTION has_bad_chars(TEXT) RETURNS BOOLEAN AS
! $function$
! BEGIN
!     RETURN ($1 ~ $q$[\t\r\n\v|\\]$q$);
! END;
! $function$ LANGUAGE plpgsql;
! </programlisting>
! 
!     <para>
!     Note that nesting requires a different identifier, as above, and
!     only works when the quoted string will be re-parsed.
!     </para>
!     <para>
!     Dollar quoting is not part of the SQL standard, but it is most
!     useful in places, like function bodies, where the SQL standard does
!     not apply.  Please not that everything inside dollar quotes is
!     passed literally.  For example, inside dollar quotes, backslash is
!     just another character with no magic attached.
!     </para>
! 
!     <para>
!     Dollar quotes are case sensitive, so
!     <literal>$quote$This$quote$</literal> is valid, but
!     <literal>$QUOTE$This$quote$</literal> is not.
      </para>
  
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html

Reply via email to