Kind people,
Please find attached a diff to the SGML docs that attempts to go over
the new dollar quoting feature.
In eager anticipation of comments & feedback,
I remain,
Your Humble Servant,
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 <record> 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 > 0 THEN
! result := ''positive'';
ELSIF number < 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 > 0 THEN
! result := 'positive';
ELSIF number < 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 < 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 < 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 <record> 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 <record> 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 >= \\$1 AND num <=
\\$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 >= \$1 AND num <=
\$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 6: Have you searched our list archives?
http://archives.postgresql.org
