<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <HTML ><HEAD ><TITLE >Tips for Developing in PL/pgSQL</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 7.4.1 Documentation" HREF="index.html"><LINK REL="UP" TITLE="PL/pgSQL - SQL Procedural Language" HREF="plpgsql.html"><LINK REL="PREVIOUS" TITLE="PL/pgSQL - SQL Procedural Language" HREF="plpgsql.html"><LINK REL="NEXT" TITLE="Structure of PL/pgSQL" HREF="plpgsql-structure.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META NAME="creation" CONTENT="2003-12-22T03:48:47"></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 7.4.1 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="plpgsql.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 37. <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-structure.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" >37.2. 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 ' .... end; ' 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 using a GUI database access tool that facilitates development in a procedural language. One example of such as a tool is <SPAN CLASS="APPLICATION" >PgAccess</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" >37.2.1. Handling of Quotation Marks</A ></H2 ><P > Since the code of a <SPAN CLASS="APPLICATION" >PL/pgSQL</SPAN > function is specified in <TT CLASS="COMMAND" >CREATE FUNCTION</TT > 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 <A HREF="plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN" >Section 37.6.4</A >. This chart may be useful as a summary of the needed numbers of quotation marks in various situations. </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 the function body, quotation 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> The second line is seen by <SPAN CLASS="APPLICATION" >PL/pgSQL</SPAN > as </P><PRE CLASS="PROGRAMLISTING" >SELECT * FROM users WHERE f_name='foobar';</PRE ><P> </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 ></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 ></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. 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 ></DD ></DL ></DIV ><P > A different approach is to escape quotation marks in the function body with a backslash rather than by doubling them. With this method you'll find yourself writing things like <TT CLASS="LITERAL" >\'\'</TT > instead of <TT CLASS="LITERAL" >''''</TT >. Some find this easier to keep track of, some do not. </P ></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.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-structure.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><SPAN CLASS="APPLICATION" >PL/pgSQL</SPAN > - <ACRONYM CLASS="ACRONYM" >SQL</ACRONYM > Procedural Language</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="plpgsql.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Structure of <SPAN CLASS="APPLICATION" >PL/pgSQL</SPAN ></TD ></TR ></TABLE ></DIV ></BODY ></HTML >