diff options
Diffstat (limited to 'doc/src/sgml/ref/create_procedure.sgml')
| -rw-r--r-- | doc/src/sgml/ref/create_procedure.sgml | 61 |
1 files changed, 56 insertions, 5 deletions
diff --git a/doc/src/sgml/ref/create_procedure.sgml b/doc/src/sgml/ref/create_procedure.sgml index 6dbc0127194..2cd47d097f3 100644 --- a/doc/src/sgml/ref/create_procedure.sgml +++ b/doc/src/sgml/ref/create_procedure.sgml @@ -29,6 +29,7 @@ CREATE [ OR REPLACE ] PROCEDURE | SET <replaceable class="parameter">configuration_parameter</replaceable> { TO <replaceable class="parameter">value</replaceable> | = <replaceable class="parameter">value</replaceable> | FROM CURRENT } | AS '<replaceable class="parameter">definition</replaceable>' | AS '<replaceable class="parameter">obj_file</replaceable>', '<replaceable class="parameter">link_symbol</replaceable>' + | <replaceable class="parameter">sql_body</replaceable> } ... </synopsis> </refsynopsisdiv> @@ -167,7 +168,9 @@ CREATE [ OR REPLACE ] PROCEDURE The name of the language that the procedure is implemented in. It can be <literal>sql</literal>, <literal>c</literal>, <literal>internal</literal>, or the name of a user-defined - procedural language, e.g., <literal>plpgsql</literal>. Enclosing the + procedural language, e.g., <literal>plpgsql</literal>. The default is + <literal>sql</literal> if <replaceable + class="parameter">sql_body</replaceable> is specified. Enclosing the name in single quotes is deprecated and requires matching case. </para> </listitem> @@ -304,6 +307,41 @@ CREATE [ OR REPLACE ] PROCEDURE </listitem> </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">sql_body</replaceable></term> + + <listitem> + <para> + The body of a <literal>LANGUAGE SQL</literal> procedure. This should + be a block +<programlisting> +BEGIN ATOMIC + <replaceable>statement</replaceable>; + <replaceable>statement</replaceable>; + ... + <replaceable>statement</replaceable>; +END +</programlisting> + </para> + + <para> + This is similar to writing the text of the procedure body as a string + constant (see <replaceable>definition</replaceable> above), but there + are some differences: This form only works for <literal>LANGUAGE + SQL</literal>, the string constant form works for all languages. This + form is parsed at procedure definition time, the string constant form is + parsed at execution time; therefore this form cannot support + polymorphic argument types and other constructs that are not resolvable + at procedure definition time. This form tracks dependencies between the + procedure and objects used in the procedure body, so <literal>DROP + ... CASCADE</literal> will work correctly, whereas the form using + string literals may leave dangling procedures. Finally, this form is + more compatible with the SQL standard and other SQL implementations. + </para> + </listitem> + </varlistentry> + </variablelist> </refsect1> @@ -323,6 +361,7 @@ CREATE [ OR REPLACE ] PROCEDURE <refsect1 id="sql-createprocedure-examples"> <title>Examples</title> + <para> <programlisting> CREATE PROCEDURE insert_data(a integer, b integer) LANGUAGE SQL @@ -330,9 +369,21 @@ AS $$ INSERT INTO tbl VALUES (a); INSERT INTO tbl VALUES (b); $$; - +</programlisting> + or +<programlisting> +CREATE PROCEDURE insert_data(a integer, b integer) +LANGUAGE SQL +BEGIN ATOMIC + INSERT INTO tbl VALUES (a); + INSERT INTO tbl VALUES (b); +END; +</programlisting> + and call like this: +<programlisting> CALL insert_data(1, 2); </programlisting> + </para> </refsect1> <refsect1 id="sql-createprocedure-compat"> @@ -340,9 +391,9 @@ CALL insert_data(1, 2); <para> A <command>CREATE PROCEDURE</command> command is defined in the SQL - standard. The <productname>PostgreSQL</productname> version is similar but - not fully compatible. For details see - also <xref linkend="sql-createfunction"/>. + standard. The <productname>PostgreSQL</productname> implementation can be + used in a compatible way but has many extensions. For details see also + <xref linkend="sql-createfunction"/>. </para> </refsect1> |