8000 SQL procedures · ololobus/postgres@e4128ee · GitHub
[go: up one dir, main page]
Skip to content

Commit e4128ee

Browse files
peterepetere
committed
SQL procedures
This adds a new object type "procedure" that is similar to a function but does not have a return type and is invoked by the new CALL statement instead of SELECT or similar. This implementation is aligned with the SQL standard and compatible with or similar to other SQL implementations. This commit adds new commands CALL, CREATE/ALTER/DROP PROCEDURE, as well as ALTER/DROP ROUTINE that can refer to either a function or a procedure (or an aggregate function, as an extension to SQL). There is also support for procedures in various utility commands such as COMMENT and GRANT, as well as support in pg_dump and psql. Support for defining procedures is available in all the languages supplied by the core distribution. While this commit is mainly syntax sugar around existing functionality, future features will rely on having procedures as a separate object type. Reviewed-by: Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
1 parent 1761653 commit e4128ee
  • pltcl.c
  • sql
  • test/regress

    • Some content is hidden

    Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

    92 files changed

    +2951
    -305
    lines changed

    ‎doc/src/sgml/catalogs.sgml

    Lines changed: 1 addition & 1 deletion
    Original file line numberDiff line numberDiff line change
    @@ -5241,7 +5241,7 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
    52415241
    <entry><structfield>prorettype</structfield></entry>
    52425242
    <entry><type>oid</type></entry>
    52435243
    <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
    5244-
    <entry>Data type of the return value</entry>
    5244+
    <entry>Data type of the return value, or null for a procedure</entry>
    52455245
    </row>
    52465246

    52475247
    <row>

    ‎doc/src/sgml/ddl.sgml

    Lines changed: 1 addition & 1 deletion
    Original file line numberDiff line numberDiff line change
    @@ -3947,7 +3947,7 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate &gt;= DATE '2008-01-01';
    39473947

    39483948
    <listitem>
    39493949
    <para>
    3950-
    Functions and operators
    3950+
    Functions, procedures, and operators
    39513951
    </para>
    39523952
    </listitem>
    39533953

    ‎doc/src/sgml/ecpg.sgml

    Lines changed: 3 additions & 1 deletion
    Original file line numberDiff line numberDiff line change
    @@ -4778,7 +4778,9 @@ EXEC SQL WHENEVER <replaceable>condition</replaceable> <replaceable>action</repl
    47784778
    <term><literal>DO <replaceable>name</replaceable> (<replaceable>args</replaceable>)</literal></term>
    47794779
    <listitem>
    47804780
    <para>
    4781-
    Call the specified C functions with the specified arguments.
    4781+
    Call the specified C functions with the specified arguments. (This
    4782+
    use is different from the meaning of <literal>CALL</literal>
    4783+
    and <literal>DO</literal> in the normal PostgreSQL grammar.)
    47824784
    </para>
    47834785
    </listitem>
    47844786
    </varlistentry>

    ‎doc/src/sgml/information_schema.sgml

    Lines changed: 9 additions & 9 deletions
    Original file line numberDiff line numberDiff line change
    @@ -3972,8 +3972,8 @@ ORDER BY c.ordinal_position;
    39723972
    <title><literal>routines</literal></title>
    39733973

    39743974
    <para>
    3975-
    The view <literal>routines</literal> contains all functions in the
    3976-
    current database. Only those functions are shown that the current
    3975+
    The view <literal>routines</literal> contains all functions and procedures in the
    3976+
    current database. Only those functions and procedures are shown that the current
    39773977
    user has access to (by way of being the owner or having some
    39783978
    privilege).
    39793979
    </para>
    @@ -4037,8 +4037,8 @@ ORDER BY c.ordinal_position;
    40374037
    <entry><literal>routine_type</literal></entry>
    40384038
    <entry><type>character_data</type></entry>
    40394039
    <entry>
    4040-
    Always <literal>FUNCTION</literal> (In the future there might
    4041-
    be other types of routines.)
    4040+
    <literal>FUNCTION</literal> for a
    4041+
    function, <literal>PROCEDURE</literal> for a procedure
    40424042
    </entry>
    40434043
    </row>
    40444044

    @@ -4087,7 +4087,7 @@ ORDER BY c.ordinal_position;
    40874087
    the view <literal>element_types</literal>), else
    40884088
    <literal>USER-DEFINED</literal> (in that case, the type is
    40894089
    identified in <literal>type_udt_name</literal> and associated
    4090-
    columns).
    4090+
    columns). Null for a procedure.
    40914091
    </entry>
    40924092
    </row>
    40934093

    @@ -4180,7 +4180,7 @@ ORDER BY c.ordinal_position;
    41804180
    <entry><type>sql_identifier</type></entry>
    41814181
    <entry>
    41824182
    Name of the database that the return data type of the function
    4183-
    is defined in (always the current database)
    4183+
    is defined in (always the current database). Null for a procedure.
    41844184
    </entry>
    41854185
    </row>
    41864186

    @@ -4189,15 +4189,15 @@ ORDER BY c.ordinal_position;
    41894189
    <entry><type>sql_identifier</type></entry>
    41904190
    <entry>
    41914191
    Name of the schema that the return data type of the function is
    4192-
    defined in
    4192+
    defined in. Null for a procedure.
    41934193
    </entry>
    41944194
    </row>
    41954195

    41964196
    <row>
    41974197
    <entry><literal>type_udt_name</literal></entry>
    41984198
    <entry><type>sql_identifier</type></entry>
    41994199
    <entry>
    4200-
    Name of the return data type of the function
    4200+
    Name of the return data type of the function. Null for a procedure.
    42014201
    </entry>
    42024202
    </row>
    42034203

    @@ -4314,7 +4314,7 @@ ORDER BY c.ordinal_position;
    43144314
    <entry>
    43154315
    If the function automatically returns null if any of its
    43164316
    arguments are null, then <literal>YES</literal>, else
    4317-
    <literal>NO</literal>.
    4317+
    <literal>NO</literal>. Null for a procedure.
    43184318
    </entry>
    43194319
    </row>
    43204320

    ‎doc/src/sgml/plperl.sgml

    Lines changed: 4 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -67,6 +67,10 @@ $$ LANGUAGE plperl;
    6767
    as discussed below.
    6868
    </para>
    6969

    70+
    <para>
    71+
    In a PL/Perl procedure, any return value from the Perl code is ignored.
    72+
    </para>
    73+
    7074
    <para>
    7175
    PL/Perl also supports anonymous code blocks called with the
    7276
    <xref linkend="sql-do"/> statement:

    ‎doc/src/sgml/plpgsql.sgml

    Lines changed: 15 additions & 2 deletions
    Original file line numberDiff line numberDiff line change
    @@ -156,7 +156,8 @@
    156156

    157157
    <para>
    158158
    Finally, a <application>PL/pgSQL</application> function can be declared to return
    159-
    <type>void</type> if it has no useful return value.
    159+
    <type>void</type> if it has no useful return value. (Alternatively, it
    160+
    could be written as a procedure in that case.)
    160161
    </para>
    161162

    162163
    <para>
    @@ -1865,6 +1866,18 @@ SELECT * FROM get_available_flightid(CURRENT_DATE);
    18651866
    </sect3>
    18661867
    </sect2>
    18671868

    1869+
    <sect2 id="plpgsql-statements-returning-procedure">
    1870+
    <title>Returning From a Procedure</title>
    1871+
    1872+
    <para>
    1873+
    A procedure does not have a return value. A procedure can therefore end
    1874+
    without a <command>RETURN</command> statement. If
    1875+
    a <command>RETURN</command> statement is desired to exit the code early,
    1876+
    then <symbol>NULL</symbol> must be returned. Returning any other value
    1877+
    will result in an error.
    1878+
    </para>
    1879+
    </sect2>
    1880+
    18681881
    <sect2 id="plpgsql-conditionals">
    18691882
    <title>Conditionals</title>
    18701883

    @@ -5244,7 +5257,7 @@ show errors;
    52445257
    <para>
    52455258
    Here is how this function would end up in <productname>PostgreSQL</productname>:
    52465259
    <programlisting>
    5247-
    CREATE OR REPLACE FUNCTION cs_update_referrer_type_proc() RETURNS void AS $func$
    5260+
    CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc() AS $func$
    52485261
    DECLARE
    52495262
    referrer_keys CURSOR IS
    52505263
    SELECT * FROM cs_referrer_keys

    ‎doc/src/sgml/plpython.sgml

    Lines changed: 5 additions & 1 deletion
    Original file line numberDiff line numberDiff line change
    @@ -207,7 +207,11 @@ $$ LANGUAGE plpythonu;
    207207
    <literal>yield</literal> (in case of a result-set statement). If
    208208
    you do not provide a return value, Python returns the default
    209209
    <symbol>None</symbol>. <application>PL/Python</application> translates
    210-
    Python's <symbol>None</symbol> into the SQL null value.
    210+
    Python's <symbol>None</symbol> into the SQL null value. In a procedure,
    211+
    the result from the Python code must be <symbol>None</symbol> (typically
    212+
    achieved by ending the procedure without a <literal>return</literal>
    213+
    statement or by using a <literal>return</literal> statement without
    214+
    argument); otherwise, an error will be raised.
    211215
    </para>
    212216

    213217
    <para>

    ‎doc/src/sgml/pltcl.sgml

    Lines changed: 2 additions & 1 deletion
    Original file line numberDiff line numberDiff line change
    @@ -97,7 +97,8 @@ $$ LANGUAGE pltcl;
    9797
    Tcl script as variables named <literal>1</literal>
    9898
    ... <literal><replaceable>n</replaceable></literal>. The result is
    9999
    returned from the Tcl code in the usual way, with
    100-
    a <literal>return</literal> statement.
    100+
    a <literal>return</literal> statement. In a procedure, the return value
    101+
    from the Tcl code is ignored.
    101102
    </para>
    102103

    103104
    <para>

    ‎doc/src/sgml/ref/allfiles.sgml

    Lines changed: 6 additions & 0 deletions
    123
    Original file line numberDiff line numberDiff line change
    @@ -26,8 +26,10 @@ Complete list of usable sgml source files in this directory.
    2626
    <!ENTITY alterOperatorClass SYSTEM "alter_opclass.sgml">
    2727
    <!ENTITY alterOperatorFamily SYSTEM "alter_opfamily.sgml">
    2828
    <!ENTITY alterPolicy SYSTEM "alter_policy.sgml">
    29+
    <!ENTITY alterProcedure SYSTEM "alter_procedure.sgml">
    2930
    <!ENTITY alterPublication SYSTEM "alter_publication.sgml">
    3031
    <!ENTITY alterRole SYSTEM "alter_role.sgml">
    32+
    <!ENTITY alterRoutine SYSTEM "alter_routine.sgml">
    3133
    <!ENTITY alterRule SYSTEM "alter_rule.sgml">
    3234
    <!ENTITY alterSchema SYSTEM "alter_schema.sgml">
    3335
    <!ENTITY alterServer SYSTEM "alter_server.sgml">
    @@ -48,6 +50,7 @@ Complete list of usable sgml source files in this directory.
    4850
    <!ENTITY alterView SYSTEM "alter_view.sgml">
    4951
    <!ENTITY analyze SYSTEM "analyze.sgml">
    5052
    <!ENTITY begin SYSTEM "begin.sgml">
    53+
    <!ENTITY call SYSTEM "call.sgml">
    5154
    <!ENTITY checkpoint SYSTEM "checkpoint.sgml">
    5255
    <!ENTITY close SYSTEM "close.sgml">
    5356
    <!ENTITY cluster SYSTEM "cluster.sgml">
    @@ -75,6 +78,7 @@ Complete list of usable sgml source files in this directory.
    7578
    <!ENTITY createOperatorClass SYSTEM "create_opclass.sgml">
    7679
    <!ENTITY createOperatorFamily SYSTEM "create_opfamily.sgml">
    7780
    <!ENTITY createPolicy SYSTEM "create_policy.sgml">
    81+
    <!ENTITY createProcedure SYSTEM "create_procedure.sgml">
    7882
    <!ENTITY createPublication SYSTEM "create_publication.sgml">
    7983
    <!ENTITY createRole SYSTEM "create_role.sgml">
    8084
    <!ENTITY createRule SYSTEM "create_rule.sgml">
    @@ -122,8 +126,10 @@ Complete list of usable sgml source files in this directory.
    122126
    <!ENTITY dropOperatorFamily SYSTEM "drop_opfamily.sgml">
    127
    <!ENTITY dropOwned SYSTEM "drop_owned.sgml">
    124128
    <!ENTITY dropPolicy SYSTEM "drop_policy.sgml">
    129+
    <!ENTITY dropProcedure SYSTEM "drop_procedure.sgml">
    125130
    <!ENTITY dropPublication SYSTEM "drop_publication.sgml">
    126131
    <!ENTITY dropRole SYSTEM "drop_role.sgml">
    132+
    <!ENTITY dropRoutine SYSTEM "drop_routine.sgml">
    127133
    <!ENTITY dropRule SYSTEM "drop_rule.sgml">
    128134
    <!ENTITY dropSchema SYSTEM "drop_schema.sgml">
    129135
    <!ENTITY dropSequence SYSTEM "drop_sequence.sgml">

    ‎doc/src/sgml/ref/alter_default_privileges.sgml

    Lines changed: 9 additions & 3 deletions
    Original file line numberDiff line numberDiff line change
    @@ -39,7 +39,7 @@ GRANT { { USAGE | SELECT | UPDATE }
    3939
    TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
    4040

    4141
    GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    42-
    ON FUNCTIONS
    42+
    ON { FUNCTIONS | ROUTINES }
    4343
    TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
    4444

    4545
    GRANT { USAGE | ALL [ PRIVILEGES ] }
    @@ -66,7 +66,7 @@ REVOKE [ GRANT OPTION FOR ]
    6666

    6767
    REVOKE [ GRANT OPTION FOR ]
    6868
    { EXECUTE | ALL [ PRIVILEGES ] }
    69-
    ON FUNCTIONS
    69+
    ON { FUNCTIONS | ROUTINES }
    7070
    FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...]
    7171
    [ CASCADE | RESTRICT ]
    7272

    @@ -93,7 +93,13 @@ REVOKE [ GRANT OPTION FOR ]
    9393
    affect privileges assigned to already-existing objects.) Currently,
    9494
    only the privileges for schemas, tables (including views and foreign
    9595
    tables), sequences, functions, and types (including domains) can be
    96-
    altered.
    96+
    altered. For this command, functions include aggregates and procedures.
    97+
    The words <literal>FUNCTIONS</literal> and <literal>ROUTINES</literal> are
    98+
    equivalent in this command. (<literal>ROUTINES</literal> is preferred
    99+
    going forward as the standard term for functions and procedures taken
    100+
    together. In earlier PostgreSQL releases, only the
    101+
    word <literal>FUNCTIONS</literal> was allowed. It is not possible to set
    102+
    default privileges for functions and procedures separately.)
    97103
    </para>
    98104

    99105
    <para>

    ‎doc/src/sgml/ref/alter_extension.sgml

    Lines changed: 8 additions & 4 deletions
    Original file line numberDiff line numberDiff line change
    @@ -45,6 +45,8 @@ ALTER EXTENSION <replaceable class="parameter">name</replaceable> DROP <replacea
    4545
    OPERATOR CLASS <replaceable class="parameter">object_name</replaceable> USING <replaceable class="parameter">index_method</replaceable> |
    4646
    OPERATOR FAMILY <replaceable class="parameter">object_name</replaceable> USING <replaceable class="parameter">index_method</replaceable> |
    4747
    [ PROCEDURAL ] LANGUAGE <replaceable class="parameter">object_name</replaceable> |
    48+
    PROCEDURE <replaceable class="parameter">procedure_name</replaceable> [ ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) ] |
    49+
    ROUTINE <replaceable class="parameter">routine_name</replaceable> [ ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) ] |
    4850
    SCHEMA <replaceable class="parameter">object_name</replaceable> |
    4951
    SEQUENCE <replaceable class="parameter">object_name</replaceable> |
    5052
    SERVER <replaceable class="parameter">object_name</replaceable> |
    @@ -170,12 +172,14 @@ ALTER EXTENSION <replaceable class="parameter">name</replaceable> DROP <replacea
    170172
    <term><replaceable class="parameter">aggregate_name</replaceable></term>
    171173
    <term><replaceable class="parameter">function_name</replaceable></term>
    172174
    <term><replaceable class="parameter">operator_name</replaceable></term>
    175+
    <term><replaceable class="parameter">procedure_name</replaceable></term>
    176+
    <term><replaceable class="parameter">routine_name</replaceable></term>
    173177
    <listitem>
    174178
    <para>
    175179
    The name of an object to be added to or removed from the extension.
    176180
    Names of tables,
    177181
    aggregates, domains, foreign tables, functions, operators,
    178-
    operator classes, operator families, sequences, text search objects,
    182+
    operator classes, operator families, procedures, routines, sequences, text search objects,
    179183
    types, and views can be schema-qualified.
    180184
    </para>
    181185
    </listitem>
    @@ -204,7 +208,7 @@ ALTER EXTENSION <replaceable class="parameter">name</replaceable> DROP <replacea
    204208

    205209
    <listitem>
    206210
    <para>
    207-
    The mode of a function or aggregate
    211+
    The mode of a function, procedure, or aggregate
    208212
    argument: <literal>IN</literal>, <literal>OUT</literal>,
    209213
    <literal>INOUT</literal>, or <literal>VARIADIC</literal>.
    210214
    If omitted, the default is <literal>IN</literal>.
    @@ -222,7 +226,7 @@ ALTER EXTENSION <replaceable class="parameter">name</replaceable> DROP <replacea
    222226

    223227
    <listitem>
    224228
    <para>
    225-
    The name of a function or aggregate argument.
    229+
    The name of a function, procedure, or aggregate argument.
    226230
    Note that <command>ALTER EXTENSION</command> does not actually pay
    227231
    any attention to argument names, since only the argument data
    228232
    types are needed to determine the function's identity.
    @@ -235,7 +239,7 @@ ALTER EXTENSION <replaceable class="parameter">name</replaceable> DROP <replacea
    235239

    236240
    <listitem>
    237241
    <para>
    238-
    The data type of a function or aggregate argument.
    242+
    The data type of a function, procedure, or aggregate argument.
    239243
    </para>
    240244
    </listitem>
    241245
    </varlistentry>

    ‎doc/src/sgml/ref/alter_function.sgml

    Lines changed: 2 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -359,6 +359,8 @@ ALTER FUNCTION check_password(text) RESET search_path;
    359359
    <simplelist type="inline">
    360360
    <member><xref linkend="sql-createfunction"/></member>
    361361
    <member><xref linkend="sql-dropfunction"/></member>
    362+
    <member><xref linkend="sql-alterprocedure"/></member>
    363+
    <member><xref linkend="sql-alterroutine"/></member>
    362364
    </simplelist>
    363365
    </refsect1>
    364366
    </refentry>

    0 commit comments

    Comments
     (0)
    0