/usr/share/doc/postgresql-9.3-plproxy/syntax.html

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
    "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head>
<meta http-equiv="Content-Type" content="application/xhtml+xml; charset=UTF-8" />
<meta name="generator" content="AsciiDoc 8.6.7" />
<title>PL/Proxy Language Syntax</title>
<style type="text/css">
/* Shared CSS for AsciiDoc xhtml11 and html5 backends */

/* Default font. */
body {
  font-family: Georgia,serif;
}

/* Title font. */
h1, h2, h3, h4, h5, h6,
div.title, caption.title,
thead, p.table.header,
#toctitle,
#author, #revnumber, #revdate, #revremark,
#footer {
  font-family: Arial,Helvetica,sans-serif;
}

body {
  margin: 1em 5% 1em 5%;
}

a {
  color: blue;
  text-decoration: underline;
}
a:visited {
  color: fuchsia;
}

em {
  font-style: italic;
  color: navy;
}

strong {
  font-weight: bold;
  color: #083194;
}

h1, h2, h3, h4, h5, h6 {
  color: #527bbd;
  margin-top: 1.2em;
  margin-bottom: 0.5em;
  line-height: 1.3;
}

h1, h2, h3 {
  border-bottom: 2px solid silver;
}
h2 {
  padding-top: 0.5em;
}
h3 {
  float: left;
}
h3 + * {
  clear: left;
}
h5 {
  font-size: 1.0em;
}

div.sectionbody {
  margin-left: 0;
}

hr {
  border: 1px solid silver;
}

p {
  margin-top: 0.5em;
  margin-bottom: 0.5em;
}

ul, ol, li > p {
  margin-top: 0;
}
ul > li     { color: #aaa; }
ul > li > * { color: black; }

pre {
  padding: 0;
  margin: 0;
}

#author {
  color: #527bbd;
  font-weight: bold;
  font-size: 1.1em;
}
#email {
}
#revnumber, #revdate, #revremark {
}

#footer {
  font-size: small;
  border-top: 2px solid silver;
  padding-top: 0.5em;
  margin-top: 4.0em;
}
#footer-text {
  float: left;
  padding-bottom: 0.5em;
}
#footer-badges {
  float: right;
  padding-bottom: 0.5em;
}

#preamble {
  margin-top: 1.5em;
  margin-bottom: 1.5em;
}
div.imageblock, div.exampleblock, div.verseblock,
div.quoteblock, div.literalblock, div.listingblock, div.sidebarblock,
div.admonitionblock {
  margin-top: 1.0em;
  margin-bottom: 1.5em;
}
div.admonitionblock {
  margin-top: 2.0em;
  margin-bottom: 2.0em;
  margin-right: 10%;
  color: #606060;
}

div.content { /* Block element content. */
  padding: 0;
}

/* Block element titles. */
div.title, caption.title {
  color: #527bbd;
  font-weight: bold;
  text-align: left;
  margin-top: 1.0em;
  margin-bottom: 0.5em;
}
div.title + * {
  margin-top: 0;
}

td div.title:first-child {
  margin-top: 0.0em;
}
div.content div.title:first-child {
  margin-top: 0.0em;
}
div.content + div.title {
  margin-top: 0.0em;
}

div.sidebarblock > div.content {
  background: #ffffee;
  border: 1px solid #dddddd;
  border-left: 4px solid #f0f0f0;
  padding: 0.5em;
}

div.listingblock > div.content {
  border: 1px solid #dddddd;
  border-left: 5px solid #f0f0f0;
  background: #f8f8f8;
  padding: 0.5em;
}

div.quoteblock, div.verseblock {
  padding-left: 1.0em;
  margin-left: 1.0em;
  margin-right: 10%;
  border-left: 5px solid #f0f0f0;
  color: #888;
}

div.quoteblock > div.attribution {
  padding-top: 0.5em;
  text-align: right;
}

div.verseblock > pre.content {
  font-family: inherit;
  font-size: inherit;
}
div.verseblock > div.attribution {
  padding-top: 0.75em;
  text-align: left;
}
/* DEPRECATED: Pre version 8.2.7 verse style literal block. */
div.verseblock + div.attribution {
  text-align: left;
}

div.admonitionblock .icon {
  vertical-align: top;
  font-size: 1.1em;
  font-weight: bold;
  text-decoration: underline;
  color: #527bbd;
  padding-right: 0.5em;
}
div.admonitionblock td.content {
  padding-left: 0.5em;
  border-left: 3px solid #dddddd;
}

div.exampleblock > div.content {
  border-left: 3px solid #dddddd;
  padding-left: 0.5em;
}

div.imageblock div.content { padding-left: 0; }
span.image img { border-style: none; }
a.image:visited { color: white; }

dl {
  margin-top: 0.8em;
  margin-bottom: 0.8em;
}
dt {
  margin-top: 0.5em;
  margin-bottom: 0;
  font-style: normal;
  color: navy;
}
dd > *:first-child {
  margin-top: 0.1em;
}

ul, ol {
    list-style-position: outside;
}
ol.arabic {
  list-style-type: decimal;
}
ol.loweralpha {
  list-style-type: lower-alpha;
}
ol.upperalpha {
  list-style-type: upper-alpha;
}
ol.lowerroman {
  list-style-type: lower-roman;
}
ol.upperroman {
  list-style-type: upper-roman;
}

div.compact ul, div.compact ol,
div.compact p, div.compact p,
div.compact div, div.compact div {
  margin-top: 0.1em;
  margin-bottom: 0.1em;
}

tfoot {
  font-weight: bold;
}
td > div.verse {
  white-space: pre;
}

div.hdlist {
  margin-top: 0.8em;
  margin-bottom: 0.8em;
}
div.hdlist tr {
  padding-bottom: 15px;
}
dt.hdlist1.strong, td.hdlist1.strong {
  font-weight: bold;
}
td.hdlist1 {
  vertical-align: top;
  font-style: normal;
  padding-right: 0.8em;
  color: navy;
}
td.hdlist2 {
  vertical-align: top;
}
div.hdlist.compact tr {
  margin: 0;
  padding-bottom: 0;
}

.comment {
  background: yellow;
}

.footnote, .footnoteref {
  font-size: 0.8em;
}

span.footnote, span.footnoteref {
  vertical-align: super;
}

#footnotes {
  margin: 20px 0 20px 0;
  padding: 7px 0 0 0;
}

#footnotes div.footnote {
  margin: 0 0 5px 0;
}

#footnotes hr {
  border: none;
  border-top: 1px solid silver;
  height: 1px;
  text-align: left;
  margin-left: 0;
  width: 20%;
  min-width: 100px;
}

div.colist td {
  padding-right: 0.5em;
  padding-bottom: 0.3em;
  vertical-align: top;
}
div.colist td img {
  margin-top: 0.3em;
}

@media print {
  #footer-badges { display: none; }
}

#toc {
  margin-bottom: 2.5em;
}

#toctitle {
  color: #527bbd;
  font-size: 1.1em;
  font-weight: bold;
  margin-top: 1.0em;
  margin-bottom: 0.1em;
}

div.toclevel0, div.toclevel1, div.toclevel2, div.toclevel3, div.toclevel4 {
  margin-top: 0;
  margin-bottom: 0;
}
div.toclevel2 {
  margin-left: 2em;
  font-size: 0.9em;
}
div.toclevel3 {
  margin-left: 4em;
  font-size: 0.9em;
}
div.toclevel4 {
  margin-left: 6em;
  font-size: 0.9em;
}

span.aqua { color: aqua; }
span.black { color: black; }
span.blue { color: blue; }
span.fuchsia { color: fuchsia; }
span.gray { color: gray; }
span.green { color: green; }
span.lime { color: lime; }
span.maroon { color: maroon; }
span.navy { color: navy; }
span.olive { color: olive; }
span.purple { color: purple; }
span.red { color: red; }
span.silver { color: silver; }
span.teal { color: teal; }
span.white { color: white; }
span.yellow { color: yellow; }

span.aqua-background { background: aqua; }
span.black-background { background: black; }
span.blue-background { background: blue; }
span.fuchsia-background { background: fuchsia; }
span.gray-background { background: gray; }
span.green-background { background: green; }
span.lime-background { background: lime; }
span.maroon-background { background: maroon; }
span.navy-background { background: navy; }
span.olive-background { background: olive; }
span.purple-background { background: purple; }
span.red-background { background: red; }
span.silver-background { background: silver; }
span.teal-background { background: teal; }
span.white-background { background: white; }
span.yellow-background { background: yellow; }

span.big { font-size: 2em; }
span.small { font-size: 0.6em; }

span.underline { text-decoration: underline; }
span.overline { text-decoration: overline; }
span.line-through { text-decoration: line-through; }

div.unbreakable { page-break-inside: avoid; }


/*
 * xhtml11 specific
 *
 * */

tt {
  font-family: "Courier New", Courier, monospace;
  font-size: inherit;
  color: navy;
}

div.tableblock {
  margin-top: 1.0em;
  margin-bottom: 1.5em;
}
div.tableblock > table {
  border: 3px solid #527bbd;
}
thead, p.table.header {
  font-weight: bold;
  color: #527bbd;
}
p.table {
  margin-top: 0;
}
/* Because the table frame attribute is overriden by CSS in most browsers. */
div.tableblock > table[frame="void"] {
  border-style: none;
}
div.tableblock > table[frame="hsides"] {
  border-left-style: none;
  border-right-style: none;
}
div.tableblock > table[frame="vsides"] {
  border-top-style: none;
  border-bottom-style: none;
}


/*
 * html5 specific
 *
 * */

.monospaced {
  font-family: "Courier New", Courier, monospace;
  font-size: inherit;
  color: navy;
}

table.tableblock {
  margin-top: 1.0em;
  margin-bottom: 1.5em;
}
thead, p.tableblock.header {
  font-weight: bold;
  color: #527bbd;
}
p.tableblock {
  margin-top: 0;
}
table.tableblock {
  border-width: 3px;
  border-spacing: 0px;
  border-style: solid;
  border-color: #527bbd;
  border-collapse: collapse;
}
th.tableblock, td.tableblock {
  border-width: 1px;
  padding: 4px;
  border-style: solid;
  border-color: #527bbd;
}

table.tableblock.frame-topbot {
  border-left-style: hidden;
  border-right-style: hidden;
}
table.tableblock.frame-sides {
  border-top-style: hidden;
  border-bottom-style: hidden;
}
table.tableblock.frame-none {
  border-style: hidden;
}

th.tableblock.halign-left, td.tableblock.halign-left {
  text-align: left;
}
th.tableblock.halign-center, td.tableblock.halign-center {
  text-align: center;
}
th.tableblock.halign-right, td.tableblock.halign-right {
  text-align: right;
}

th.tableblock.valign-top, td.tableblock.valign-top {
  vertical-align: top;
}
th.tableblock.valign-middle, td.tableblock.valign-middle {
  vertical-align: middle;
}
th.tableblock.valign-bottom, td.tableblock.valign-bottom {
  vertical-align: bottom;
}


/*
 * manpage specific
 *
 * */

body.manpage h1 {
  padding-top: 0.5em;
  padding-bottom: 0.5em;
  border-top: 2px solid silver;
  border-bottom: 2px solid silver;
}
body.manpage h2 {
  border-style: none;
}
body.manpage div.sectionbody {
  margin-left: 3em;
}

@media print {
  body.manpage div#toc { display: none; }
}
</style>
<script type="text/javascript">
/*<![CDATA[*/
var asciidoc = {  // Namespace.

/////////////////////////////////////////////////////////////////////
// Table Of Contents generator
/////////////////////////////////////////////////////////////////////

/* Author: Mihai Bazon, September 2002
 * http://students.infoiasi.ro/~mishoo
 *
 * Table Of Content generator
 * Version: 0.4
 *
 * Feel free to use this script under the terms of the GNU General Public
 * License, as long as you do not remove or alter this notice.
 */

 /* modified by Troy D. Hanson, September 2006. License: GPL */
 /* modified by Stuart Rackham, 2006, 2009. License: GPL */

// toclevels = 1..4.
toc: function (toclevels) {

  function getText(el) {
    var text = "";
    for (var i = el.firstChild; i != null; i = i.nextSibling) {
      if (i.nodeType == 3 /* Node.TEXT_NODE */) // IE doesn't speak constants.
        text += i.data;
      else if (i.firstChild != null)
        text += getText(i);
    }
    return text;
  }

  function TocEntry(el, text, toclevel) {
    this.element = el;
    this.text = text;
    this.toclevel = toclevel;
  }

  function tocEntries(el, toclevels) {
    var result = new Array;
    var re = new RegExp('[hH]([1-'+(toclevels+1)+'])');
    // Function that scans the DOM tree for header elements (the DOM2
    // nodeIterator API would be a better technique but not supported by all
    // browsers).
    var iterate = function (el) {
      for (var i = el.firstChild; i != null; i = i.nextSibling) {
        if (i.nodeType == 1 /* Node.ELEMENT_NODE */) {
          var mo = re.exec(i.tagName);
          if (mo && (i.getAttribute("class") || i.getAttribute("className")) != "float") {
            result[result.length] = new TocEntry(i, getText(i), mo[1]-1);
          }
          iterate(i);
        }
      }
    }
    iterate(el);
    return result;
  }

  var toc = document.getElementById("toc");
  if (!toc) {
    return;
  }

  // Delete existing TOC entries in case we're reloading the TOC.
  var tocEntriesToRemove = [];
  var i;
  for (i = 0; i < toc.childNodes.length; i++) {
    var entry = toc.childNodes[i];
    if (entry.nodeName.toLowerCase() == 'div'
     && entry.getAttribute("class")
     && entry.getAttribute("class").match(/^toclevel/))
      tocEntriesToRemove.push(entry);
  }
  for (i = 0; i < tocEntriesToRemove.length; i++) {
    toc.removeChild(tocEntriesToRemove[i]);
  }

  // Rebuild TOC entries.
  var entries = tocEntries(document.getElementById("content"), toclevels);
  for (var i = 0; i < entries.length; ++i) {
    var entry = entries[i];
    if (entry.element.id == "")
      entry.element.id = "_toc_" + i;
    var a = document.createElement("a");
    a.href = "#" + entry.element.id;
    a.appendChild(document.createTextNode(entry.text));
    var div = document.createElement("div");
    div.appendChild(a);
    div.className = "toclevel" + entry.toclevel;
    toc.appendChild(div);
  }
  if (entries.length == 0)
    toc.parentNode.removeChild(toc);
},


/////////////////////////////////////////////////////////////////////
// Footnotes generator
/////////////////////////////////////////////////////////////////////

/* Based on footnote generation code from:
 * http://www.brandspankingnew.net/archive/2005/07/format_footnote.html
 */

footnotes: function () {
  // Delete existing footnote entries in case we're reloading the footnodes.
  var i;
  var noteholder = document.getElementById("footnotes");
  if (!noteholder) {
    return;
  }
  var entriesToRemove = [];
  for (i = 0; i < noteholder.childNodes.length; i++) {
    var entry = noteholder.childNodes[i];
    if (entry.nodeName.toLowerCase() == 'div' && entry.getAttribute("class") == "footnote")
      entriesToRemove.push(entry);
  }
  for (i = 0; i < entriesToRemove.length; i++) {
    noteholder.removeChild(entriesToRemove[i]);
  }

  // Rebuild footnote entries.
  var cont = document.getElementById("content");
  var spans = cont.getElementsByTagName("span");
  var refs = {};
  var n = 0;
  for (i=0; i<spans.length; i++) {
    if (spans[i].className == "footnote") {
      n++;
      var note = spans[i].getAttribute("data-note");
      if (!note) {
        // Use [\s\S] in place of . so multi-line matches work.
        // Because JavaScript has no s (dotall) regex flag.
        note = spans[i].innerHTML.match(/\s*\[([\s\S]*)]\s*/)[1];
        spans[i].innerHTML =
          "[<a id='_footnoteref_" + n + "' href='#_footnote_" + n +
          "' title='View footnote' class='footnote'>" + n + "</a>]";
        spans[i].setAttribute("data-note", note);
      }
      noteholder.innerHTML +=
        "<div class='footnote' id='_footnote_" + n + "'>" +
        "<a href='#_footnoteref_" + n + "' title='Return to text'>" +
        n + "</a>. " + note + "</div>";
      var id =spans[i].getAttribute("id");
      if (id != null) refs["#"+id] = n;
    }
  }
  if (n == 0)
    noteholder.parentNode.removeChild(noteholder);
  else {
    // Process footnoterefs.
    for (i=0; i<spans.length; i++) {
      if (spans[i].className == "footnoteref") {
        var href = spans[i].getElementsByTagName("a")[0].getAttribute("href");
        href = href.match(/#.*/)[0];  // Because IE return full URL.
        n = refs[href];
        spans[i].innerHTML =
          "[<a href='#_footnote_" + n +
          "' title='View footnote' class='footnote'>" + n + "</a>]";
      }
    }
  }
},

install: function(toclevels) {
  var timerId;

  function reinstall() {
    asciidoc.footnotes();
    if (toclevels) {
      asciidoc.toc(toclevels);
    }
  }

  function reinstallAndRemoveTimer() {
    clearInterval(timerId);
    reinstall();
  }

  timerId = setInterval(reinstall, 500);
  if (document.addEventListener)
    document.addEventListener("DOMContentLoaded", reinstallAndRemoveTimer, false);
  else
    window.onload = reinstallAndRemoveTimer;
}

}
asciidoc.install(2);
/*]]>*/
</script>
</head>
<body class="article">
<div id="header">
<h1>PL/Proxy Language Syntax</h1>
<div id="toc">
  <div id="toctitle">Table of Contents</div>
  <noscript><p><b>JavaScript must be enabled in your browser to display the table of contents.</b></p></noscript>
</div>
</div>
<div id="content">
<div id="preamble">
<div class="sectionbody">
<div class="paragraph"><p>The language is similar to plpgsql - string quoting, comments,
semicolon at the statements end.</p></div>
<div class="paragraph"><p>It contains only 4 statements: <tt>CONNECT</tt>, <tt>CLUSTER</tt>, <tt>RUN</tt> and <tt>SELECT</tt>.</p></div>
<div class="paragraph"><p>Each function needs to have either <tt>CONNECT</tt> or pair of <tt>CLUSTER</tt> + <tt>RUN</tt> statements
to specify where to run the function.</p></div>
<div class="paragraph"><p>The <tt>SELECT</tt> statement is optional, if it is missing, there will be default
query generated based on proxy function signature.</p></div>
<div class="paragraph"><p>The <tt>RUN</tt> statment is also optional, it defaults to <tt>RUN ON ANY</tt>
which means the query will be run random partition.</p></div>
</div>
</div>
<div class="sect1">
<h2 id="_connect">CONNECT</h2>
<div class="sectionbody">
<div class="listingblock">
<div class="content">
<pre><tt>CONNECT 'libpq connstr';</tt></pre>
</div></div>
<div class="paragraph"><p>Specifies exact location where to connect and execute the query.
If several functions have same connstr, they will use same connection.</p></div>
<div class="listingblock">
<div class="content">
<pre><tt>CONNECT connect_func(...);
CONNECT argname;
CONNECT $argnr;</tt></pre>
</div></div>
<div class="paragraph"><p>Connect string is taken from function result or directly from argument.
If several functions have same connstr, they will use same connection.
<em>(New in 2.0.9)</em></p></div>
<div class="paragraph"><p><strong>NB</strong>: giving untrusted users ability to specify full connect string creates
security hole.  Eg it can used to read cleartext passwords from <tt>~/.pgpass</tt>
or <tt>pg_service.conf</tt>.  If such function cannot be avoided, it&#8217;s access rights
need to be restricted.</p></div>
</div>
</div>
<div class="sect1">
<h2 id="_cluster">CLUSTER</h2>
<div class="sectionbody">
<div class="listingblock">
<div class="content">
<pre><tt>CLUSTER 'cluster_name';</tt></pre>
</div></div>
<div class="paragraph"><p>Specifies exact cluster name to be run on.  The cluster name will
be passed to plproxy.get_cluster_* functions.</p></div>
<div class="listingblock">
<div class="content">
<pre><tt>CLUSTER cluster_func(..);</tt></pre>
</div></div>
<div class="paragraph"><p>Cluster name can be dynamically decided upon proxy function arguments.
<tt>cluster_func</tt> should return text value of final cluster name.</p></div>
</div>
</div>
<div class="sect1">
<h2 id="_run_on">RUN ON</h2>
<div class="sectionbody">
<div class="listingblock">
<div class="content">
<pre><tt>RUN ON ALL;</tt></pre>
</div></div>
<div class="paragraph"><p>Query will be run on all partitions in cluster in parallel.</p></div>
<div class="listingblock">
<div class="content">
<pre><tt>RUN ON ANY;</tt></pre>
</div></div>
<div class="paragraph"><p>Query will be run on random partition.</p></div>
<div class="listingblock">
<div class="content">
<pre><tt>RUN ON &lt;NR&gt;;</tt></pre>
</div></div>
<div class="paragraph"><p>Run on partition number <tt>&lt;NR&gt;</tt>.</p></div>
<div class="listingblock">
<div class="content">
<pre><tt>RUN ON partition_func(..);</tt></pre>
</div></div>
<div class="paragraph"><p>Run partition_func() which should return one or more hash values.  (int4)
query will be run on tagged partitions.  If more than one partition was
tagged, query will be sent in parallel to them.</p></div>
<div class="listingblock">
<div class="content">
<pre><tt>RUN ON argname;
RUN ON $1;</tt></pre>
</div></div>
<div class="paragraph"><p>Take hash value directly from function argument.  <em>(New in 2.0.8)</em></p></div>
</div>
</div>
<div class="sect1">
<h2 id="_split">SPLIT</h2>
<div class="sectionbody">
<div class="listingblock">
<div class="content">
<pre><tt>SPLIT array_arg_1 [ , array_arg_2 ... ] ;
SPLIT ALL ;</tt></pre>
</div></div>
<div class="paragraph"><p>Split the input arrays based on RUN ON statement into per-partition arrays.
This is done by evaluating RUN ON condition for each array element and building
per-partition parameter arrays for each matching partition. During execution
each tagged partition then gets its own subset of the array to process.
<em>(New in 2.1)</em></p></div>
<div class="paragraph"><p>The semantics of RUN ON statement is slightly changed with SPLIT arrays:</p></div>
<div class="listingblock">
<div class="content">
<pre><tt>RUN ON partition_func(..);</tt></pre>
</div></div>
<div class="paragraph"><p>The array is split between the partitions matching <tt>partition_func()</tt>. Any
SPLIT parameters passed to the function are actually replaced with the
individual array elements.</p></div>
<div class="listingblock">
<div class="content">
<pre><tt>RUN ON argname;
RUN ON $1;</tt></pre>
</div></div>
<div class="paragraph"><p>An array of partition numbers (or hashes) can be passed as <tt>argname</tt>. The function
shall be run on the partitions specified in the array.</p></div>
<div class="listingblock">
<div class="content">
<pre><tt>RUN ON ANY;</tt></pre>
</div></div>
<div class="paragraph"><p>Each element is assigned to random partition.</p></div>
<div class="listingblock">
<div class="content">
<pre><tt>RUN ON ALL;
RUN ON &lt;NR&gt;;</tt></pre>
</div></div>
<div class="paragraph"><p>Unaffected, except for the added overhead of array copying.</p></div>
<div class="paragraph"><p>Example:</p></div>
<div class="listingblock">
<div class="content">
<pre><tt>CREATE FUNCTION set_profiles(i_users text[], i_profiles text[])
RETURNS SETOF text AS $$
    CLUSTER 'userdb';
    SPLIT i_users, i_profiles;
    RUN ON hashtext(i_users);
$$ LANGUAGE plproxy;</tt></pre>
</div></div>
<div class="paragraph"><p>Given query:</p></div>
<div class="listingblock">
<div class="content">
<pre><tt>SELECT * FROM set_profiles(ARRAY['foo', 'bar'], ARRAY['a', 'b']);</tt></pre>
</div></div>
<div class="paragraph"><p>The hash function is called 2 times:</p></div>
<div class="listingblock">
<div class="content">
<pre><tt>SELECT * FROM hashtext('foo');
SELECT * FROM hashtext('bar');</tt></pre>
</div></div>
<div class="paragraph"><p>And target partitions get queries:</p></div>
<div class="listingblock">
<div class="content">
<pre><tt>SELECT * FROM set_profiles(ARRAY['foo'], ARRAY['a']);
SELECT * FROM set_profiles(ARRAY['bar'], ARRAY['b']);</tt></pre>
</div></div>
</div>
</div>
<div class="sect1">
<h2 id="_target">TARGET</h2>
<div class="sectionbody">
<div class="paragraph"><p>Specify function name on remote side to be called.  By default
PL/Proxy uses current function name.  <em>(New in 2.2)</em></p></div>
<div class="paragraph"><p>Following function:</p></div>
<div class="listingblock">
<div class="content">
<pre><tt>CREATE FUNCTION some_function(username text, num int4)
RETURNS SETOF text AS $$
    CLUSTER 'userdb';
    RUN ON hashtext(username);
    TARGET other_function;
$$ LANGUAGE plproxy;</tt></pre>
</div></div>
<div class="paragraph"><p>will run following query on remote side:</p></div>
<div class="listingblock">
<div class="content">
<pre><tt>SELECT * FROM other_function(username, num);</tt></pre>
</div></div>
</div>
</div>
<div class="sect1">
<h2 id="_select">SELECT</h2>
<div class="sectionbody">
<div class="listingblock">
<div class="content">
<pre><tt>SELECT .... ;</tt></pre>
</div></div>
<div class="paragraph"><p>By default, PL/Proxy generates query based on its own signature.
But this can be overrided by giving explicit <tt>SELECT</tt> statement to run.</p></div>
<div class="paragraph"><p>Everything after <tt>SELECT</tt> until semicolon is taken as SQL to be passed on.
Only argument substitution is done on the contents, otherwise the text
is unparsed.  To avoid a table column to be parsed as function argument,
table aliases should be used.</p></div>
<div class="paragraph"><p>Query result should have same number of columns as function result
and same names too.</p></div>
</div>
</div>
<div class="sect1">
<h2 id="_argument_substitution">Argument substitution</h2>
<div class="sectionbody">
<div class="paragraph"><p>Proxy function arguments can be referenced using name or <tt>$n</tt> syntax.
Everything that is not argument reference is just passed on.</p></div>
</div>
</div>
<div class="sect1">
<h2 id="_dynamic_records">Dynamic records</h2>
<div class="sectionbody">
<div class="paragraph"><p>PL/Proxy supports function returning plain RECORD type.  Such functions
need the result type specified at call site.  Main use-case is to run
random queries on partitions. <em>(New in 2.0.6)</em></p></div>
<div class="paragraph"><p>Very simple example:</p></div>
<div class="listingblock">
<div class="content">
<pre><tt>CREATE OR REPLACE FUNCTION dynamic_query(q text)
RETURNS SETOF RECORD AS $$
    CLUSTER 'mycluster';
    RUN ON ALL;
$$ LANGUAGE plproxy;</tt></pre>
</div></div>
<div class="paragraph"><p>Corresponding function in partitions:</p></div>
<div class="listingblock">
<div class="content">
<pre><tt>CREATE OR REPLACE FUNCTION dynamic_query(sql text)
RETURNS SETOF RECORD AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN EXECUTE sql
    LOOP
        RETURN NEXT rec;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;</tt></pre>
</div></div>
<div class="paragraph"><p>Sample request:</p></div>
<div class="listingblock">
<div class="content">
<pre><tt>SELECT * FROM dynamic_query('SELECT id, username FROM sometable') AS (id integer, username text);</tt></pre>
</div></div>
<div class="paragraph"><p>The types given in AS clause must match actual types from query.</p></div>
</div>
</div>
</div>
<div id="footnotes"><hr /></div>
<div id="footer">
<div id="footer-text">
Last updated 2012-11-27 13:04:52 UTC
</div>
</div>
</body>
</html>
postgresql-9.3-plproxy 2.5-3git1 / usr / share / doc / postgresql-9.3-plproxy / syntax.html
