Sophie

Sophie

distrib > Mandriva > current > i586 > media > main-updates > by-pkgid > fc62ce67f262cdcd253dc7f849ce3223 > files > 427

postgresql8.4-docs-8.4.12-0.1mdv2010.2.i586.rpm

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Tips for Developing in PL/pgSQL</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 8.4.12 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="PL/pgSQL - SQL Procedural Language"
HREF="plpgsql.html"><LINK
REL="PREVIOUS"
TITLE="PL/pgSQL Under the Hood"
HREF="plpgsql-implementation.html"><LINK
REL="NEXT"
TITLE="Porting from Oracle PL/SQL"
HREF="plpgsql-porting.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2012-05-31T23:30:11"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 8.4.12 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="plpgsql-implementation.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="plpgsql.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 38. <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> - <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> Procedural Language</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="plpgsql.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="plpgsql-porting.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="PLPGSQL-DEVELOPMENT-TIPS"
>38.11. Tips for Developing in <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
></A
></H1
><P
>    One good way to develop in
    <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> is to use the text editor of your
    choice to create your functions, and in another window, use
    <SPAN
CLASS="APPLICATION"
>psql</SPAN
> to load and test those functions.
    If you are doing it this way, it
    is a good idea to write the function using <TT
CLASS="COMMAND"
>CREATE OR
    REPLACE FUNCTION</TT
>. That way you can just reload the file to update
    the function definition.  For example:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $$
          ....
$$ LANGUAGE plpgsql;</PRE
><P>
   </P
><P
>    While running <SPAN
CLASS="APPLICATION"
>psql</SPAN
>, you can load or reload such
    a function definition file with:
</P><PRE
CLASS="PROGRAMLISTING"
>\i filename.sql</PRE
><P>
    and then immediately issue SQL commands to test the function.
   </P
><P
>    Another good way to develop in <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> is with a
    GUI database access tool that facilitates development in a
    procedural language. One example of such as a tool is
    <SPAN
CLASS="APPLICATION"
>pgAdmin</SPAN
>, although others exist. These tools often
    provide convenient features such as escaping single quotes and
    making it easier to recreate and debug functions.
   </P
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="PLPGSQL-QUOTE-TIPS"
>38.11.1. Handling of Quotation Marks</A
></H2
><P
>    The code of a <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> function is specified in
    <TT
CLASS="COMMAND"
>CREATE FUNCTION</TT
> as a string literal.  If you
    write the string literal in the ordinary way with surrounding
    single quotes, then any single quotes inside the function body
    must be doubled; likewise any backslashes must be doubled (assuming
    escape string syntax is used).
    Doubling quotes is at best tedious, and in more complicated cases
    the code can become downright incomprehensible, because you can
    easily find yourself needing half a dozen or more adjacent quote marks.
    It's recommended that you instead write the function body as a
    <SPAN
CLASS="QUOTE"
>"dollar-quoted"</SPAN
> string literal (see <A
HREF="sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING"
>Section 4.1.2.4</A
>).  In the dollar-quoting
    approach, you never double any quote marks, but instead take care to
    choose a different dollar-quoting delimiter for each level of
    nesting you need.  For example, you might write the <TT
CLASS="COMMAND"
>CREATE
    FUNCTION</TT
> command as:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$
          ....
$PROC$ LANGUAGE plpgsql;</PRE
><P>
    Within this, you might use quote marks for simple literal strings in
    SQL commands and <TT
CLASS="LITERAL"
>$$</TT
> to delimit fragments of SQL commands
    that you are assembling as strings.  If you need to quote text that
    includes <TT
CLASS="LITERAL"
>$$</TT
>, you could use <TT
CLASS="LITERAL"
>$Q$</TT
>, and so on.
   </P
><P
>    The following chart shows what you have to do when writing quote
    marks without dollar quoting.  It might be useful when translating
    pre-dollar quoting code into something more comprehensible.
  </P
><P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
>1 quotation mark</DT
><DD
><P
>      To begin and end the function body, for example:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE FUNCTION foo() RETURNS integer AS '
          ....
' LANGUAGE plpgsql;</PRE
><P>
      Anywhere within a single-quoted function body, quote marks
      <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>must</I
></SPAN
> appear in pairs.
     </P
></DD
><DT
>2 quotation marks</DT
><DD
><P
>      For string literals inside the function body, for example:
</P><PRE
CLASS="PROGRAMLISTING"
>a_output := ''Blah'';
SELECT * FROM users WHERE f_name=''foobar'';</PRE
><P>
      In the dollar-quoting approach, you'd just write:
</P><PRE
CLASS="PROGRAMLISTING"
>a_output := 'Blah';
SELECT * FROM users WHERE f_name='foobar';</PRE
><P>
      which is exactly what the <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> parser would see
      in either case.
     </P
></DD
><DT
>4 quotation marks</DT
><DD
><P
>      When you need a single quotation mark in a string constant inside the
      function body, for example:
</P><PRE
CLASS="PROGRAMLISTING"
>a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz''</PRE
><P>
      The value actually appended to <TT
CLASS="LITERAL"
>a_output</TT
> would be:
      <TT
CLASS="LITERAL"
> AND name LIKE 'foobar' AND xyz</TT
>.
     </P
><P
>      In the dollar-quoting approach, you'd write:
</P><PRE
CLASS="PROGRAMLISTING"
>a_output := a_output || $$ AND name LIKE 'foobar' AND xyz$$</PRE
><P>
      being careful that any dollar-quote delimiters around this are not
      just <TT
CLASS="LITERAL"
>$$</TT
>.
     </P
></DD
><DT
>6 quotation marks</DT
><DD
><P
>      When a single quotation mark in a string inside the function body is
      adjacent to the end of that string constant, for example:
</P><PRE
CLASS="PROGRAMLISTING"
>a_output := a_output || '' AND name LIKE ''''foobar''''''</PRE
><P>
      The value appended to <TT
CLASS="LITERAL"
>a_output</TT
> would then be:
      <TT
CLASS="LITERAL"
> AND name LIKE 'foobar'</TT
>.
     </P
><P
>      In the dollar-quoting approach, this becomes:
</P><PRE
CLASS="PROGRAMLISTING"
>a_output := a_output || $$ AND name LIKE 'foobar'$$</PRE
><P>
     </P
></DD
><DT
>10 quotation marks</DT
><DD
><P
>      When you want two single quotation marks in a string constant (which
      accounts for 8 quotation marks) and this is adjacent to the end of that
      string constant (2 more).  You will probably only need that if
      you are writing a function that generates other functions, as in
      <A
HREF="plpgsql-porting.html#PLPGSQL-PORTING-EX2"
>Example 38-7</A
>.
      For example:
</P><PRE
CLASS="PROGRAMLISTING"
>a_output := a_output || '' if v_'' ||
    referrer_keys.kind || '' like ''''''''''
    || referrer_keys.key_string || ''''''''''
    then return ''''''  || referrer_keys.referrer_type
    || ''''''; end if;'';</PRE
><P>
      The value of <TT
CLASS="LITERAL"
>a_output</TT
> would then be:
</P><PRE
CLASS="PROGRAMLISTING"
>if v_... like ''...'' then return ''...''; end if;</PRE
><P>
     </P
><P
>      In the dollar-quoting approach, this becomes:
</P><PRE
CLASS="PROGRAMLISTING"
>a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$
    || referrer_keys.key_string || $$'
    then return '$$  || referrer_keys.referrer_type
    || $$'; end if;$$;</PRE
><P>
      where we assume we only need to put single quote marks into
      <TT
CLASS="LITERAL"
>a_output</TT
>, because it will be re-quoted before use.
     </P
></DD
></DL
></DIV
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="plpgsql-implementation.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="plpgsql-porting.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> Under the Hood</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="plpgsql.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Porting from <SPAN
CLASS="PRODUCTNAME"
>Oracle</SPAN
> PL/SQL</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>

Perl :: Catalyst :: PostgreSQL :: RPM Valid HTML 4.01 Transitional