Edit C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\odbc\7.0.1\help\userguide\postgresql.13.09.html
<?xml version="1.0" encoding="utf-8"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xml:lang="en" lang="en" xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html;charset=utf-8" /> <meta http-equiv="Content-Style-Type" content="text/css" /> <!-- MOTW-DISABLED saved from url=(0014)about:internet --> <title>Stored Procedure Results</title> <link rel="StyleSheet" href="css/postgresql.css" type="text/css" media="all" /> <link rel="StyleSheet" href="css/webworks.css" type="text/css" media="all" /> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/context.js"></script> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/towwhdir.js"></script> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/wwhpagef.js"></script> <script type="text/javascript" language="JavaScript1.2"> <!-- var WebWorksRootPath = ""; // --> </script> <script type="text/javascript" language="JavaScript1.2"> <!-- // Set reference to top level help frame // var WWHFrame = WWHGetWWHFrame("", true); // --> </script> <script type="text/javascript" language="JavaScript1.2" src="scripts/expand.js"></script> </head> <body class="" style="background-color: #FFFFEE;" onload="WWHUpdate();" onunload="WWHUnload();" onkeydown="WWHHandleKeyDown((document.all||document.getElementById||document.layers)?event:null);" onkeypress="WWHHandleKeyPress((document.all||document.getElementById||document.layers)?event:null);" onkeyup="WWHHandleKeyUp((document.all||document.getElementById||document.layers)?event:null);"> <br /> <div class="WebWorks_Breadcrumbs" style="text-align: left;"> <a class="WebWorks_Breadcrumb_Link" href="postgresql.13.01.html#298034">9 The PostgreSQL Wire Protocol Driver </a> : Stored Procedure Results</div> <hr align="left" /> <blockquote> <div class="head_1"><a name="298034">Stored Procedure Results</a></div> <div class="Body"><a name="298035">PostgreSQL provides functionality to create user-defined functions. PostgreSQL does not </a>make a distinction between user-defined functions and stored procedures. To PostgreSQL, everything is a user-defined function. PostgreSQL does not define a call mechanism for invoking a user-defined function. User-defined functions must be invoked via a SQL statement. For example, given a function defined as:</div> <div class="syntax_first"><a name="268334">CREATE table foo (intcol int, varcharcol varchar(123))</a></div> <div class="syntax"><a name="268335">CREATE or REPLACE FUNCTION insertFoo</a></div> <div class="syntax"><a name="268336"> </a> (IN idVal int, IN nameVal varchar) RETURNS void</div> <div class="syntax"><a name="268337"> </a> AS $$</div> <div class="syntax"><a name="268338"> </a> insert into foo values ($1, $2);</div> <div class="syntax"><a name="268339"> </a> $$</div> <div class="syntax"><a name="268340"> </a> LANGUAGE SQL;</div> <div class="Body"><a name="268341">must be invoked natively as:</a></div> <div class="syntax_first"><a name="268342">SELECT * FROM insertFoo(100, 'Mark')</a></div> <div class="Body"><a name="323230">even though the function does not return a value or results. The Select SQL statement </a>returns a result set that has one column named insertFoo and no row data.</div> <div class="Body"><a name="268343">The PostgreSQL Wire Protocol driver supports invoking user-defined functions using the </a>ODBC call Escape. The previously described function can be invoked using:</div> <div class="syntax_first"><a name="268345">{call insertFoo(100, 'Mark')}</a></div> <div class="Body"><a name="268346">PostgreSQL functions return data from functions as a result set. If multiple output </a>parameters are specified, the values for the output parameters are returned as columns in the result set. For example, the function defined as:</div> <div class="syntax_first"><a name="268347">CREATE or REPLACE FUNCTION addValues(in v1 int, in v2 int)</a></div> <div class="syntax"><a name="268348"> </a> RETURNS int</div> <div class="syntax"><a name="268349"> </a> AS $$</div> <div class="syntax"><a name="268350"> </a> SELECT $1 + $2;</div> <div class="syntax"><a name="268351"> </a> $$</div> <div class="syntax"><a name="268352"> </a> LANGUAGE SQL;</div> <div class="Body"><a name="268353">returns a result set with a single column of type SQL_INTEGER, whereas the function </a>defined as:</div> <div class="syntax_first"><a name="268354">CREATE or REPLACE FUNCTION selectFooRow2</a></div> <div class="syntax"><a name="268355"> </a> (IN idVal int, OUT id int, OUT name varchar)</div> <div class="syntax"><a name="268356"> </a> AS $$</div> <div class="syntax"><a name="323202"> </a> select intcol, varcharcol from foo where intcol = $1;</div> <div class="syntax"><a name="268358"> </a> $$</div> <div class="syntax"><a name="268359"> </a> LANGUAGE SQL</div> <div class="Body"><a name="268360">returns a result set that contains two columns, a SQL_INTEGER id column and a </a>SQL_VARCHAR name column.</div> <div class="Body"><a name="268361">In addition, when calling PostgreSQL functions that contain output parameters, the native </a>syntax requires that the output parameter values be omitted from the function call. This, in addition to output parameter values being returned as a result set, makes the PostgreSQL behavior of calling functions different from most other databases. </div> <div class="Body"><a name="268362">The PostgreSQL Wire Protocol driver provides a mechanism that makes the invoking of </a>functions more consistent with how other databases behave. In particular, the PostgreSQL Wire Protocol driver allows parameter markers for output parameters to be specified in the function argument list when the Escape call is used. The driver allows buffers to be bound to these output parameters. When the function is executed, the output parameters are removed from the argument list sent to the server. The driver extracts the output parameter values from the result set returned by the server and updates the bound output parameter buffers with those values. For example, the function <span class="Syntax">selectFooRow2</span> described previously can be invoked as:</div> <div class="syntax_first"><a name="268363">sql = L"{call selectFooRow2(?, ?, ?)}";</a></div> <div class="syntax"><a name="268364">retVal = SQLPrepare(hPrepStmt, sql, SQL_NTS);</a></div> <div class="syntax"><a name="268365">retVal = SQLBindParameter(</a></div> <div class="syntax"><a name="268366"> </a> hPrepStmt, 1, SQL_PARAM_INPUT, SQL_C_LONG,</div> <div class="syntax"><a name="268367"> </a> SQL_INTEGER, 0, 0, &idBuf, 0, &idInd);</div> <div class="syntax"><a name="268368">retVal = SQLBindParameter(</a></div> <div class="syntax"><a name="268369"> </a> hPrepStmt, 2, SQL_PARAM_OUTPUT, SQL_C_LONG,</div> <div class="syntax"><a name="268370"> </a> SQL_INTEGER, 0, 0, &idBuf2, 4, &idInd2);</div> <div class="syntax"><a name="268371">retVal = SQLBindParameter(</a></div> <div class="syntax"><a name="268372"> </a> hPrepStmt, 3, SQL_PARAM_OUTPUT, SQL_C_WCHAR,</div> <div class="syntax"><a name="268373"> </a> SQL_VARCHAR, 30, 0, &nameBuf, 123, &nameInd);</div> <div class="syntax"><a name="268374">retVal = SQLExecute(hPrepStmt);</a></div> <div class="Body"><a name="268375">The values of the id and name output parameters are returned in the </a><span class="Syntax">idBuf2</span> and <span class="Syntax">nameBuf</span> buffers. </div> <div class="Body"><a name="268376">If output parameters are bound to a function call, the driver returns the output parameters in </a>the bound buffers. An error is returned if the number of output parameters bound when the function is executed is less than the number of output parameters defined in the function. If no output parameters are bound to a function call, the driver returns the output parameters as a result set.</div> <div class="Body"><a name="268377">PostgreSQL can also return results from a function as a refcursor. There can be, at most, </a>one refcursor per result; however, a function can return multiple results where each result is a refcursor. A connection option defines how the driver handles refcursors. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'postgresql.13.04.html#124905', '');">â??Fetch Ref Cursorsâ??</a></span> for details about this option.</div> <script type="text/javascript" language="JavaScript1.2"> <!-- // Clear related topics // WWHClearRelatedTopics(); document.writeln(WWHRelatedTopicsInlineHTML()); // --> </script> </blockquote> <script type="text/javascript" language="JavaScript1.2"> <!-- document.write(WWHRelatedTopicsDivTag() + WWHPopupDivTag() + WWHALinksDivTag()); // --> </script> </body> </html>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de