Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commite4128ee

Browse files
committed
SQL procedures
This adds a new object type "procedure" that is similar to a functionbut does not have a return type and is invoked by the new CALL statementinstead of SELECT or similar. This implementation is aligned with theSQL standard and compatible with or similar to other SQL implementations.This commit adds new commands CALL, CREATE/ALTER/DROP PROCEDURE, as wellas ALTER/DROP ROUTINE that can refer to either a function or aprocedure (or an aggregate function, as an extension to SQL). There isalso support for procedures in various utility commands such as COMMENTand GRANT, as well as support in pg_dump and psql. Support for definingprocedures is available in all the languages supplied by the coredistribution.While this commit is mainly syntax sugar around existing functionality,future features will rely on having procedures as a separate objecttype.Reviewed-by: Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
1 parent1761653 commite4128ee

File tree

92 files changed

+2951
-305
lines changed

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 functionsand proceduresin the
3976+
current database. Only those functionsand proceduresare 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 REPLACEFUNCTION cs_update_referrer_type_proc() RETURNS void AS $func$
5260+
CREATE OR REPLACEPROCEDURE 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
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">
123127
<!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)
[8]ページ先頭
©2009-2025 Movatter.jp