2 doc/src/sgml/ref/create_language.sgml
3 PostgreSQL documentation
6 <refentry id="SQL-CREATELANGUAGE">
8 <refentrytitle>CREATE LANGUAGE</refentrytitle>
9 <manvolnum>7</manvolnum>
10 <refmiscinfo>SQL - Language Statements</refmiscinfo>
14 <refname>CREATE LANGUAGE</refname>
15 <refpurpose>define a new procedural language</refpurpose>
18 <indexterm zone="sql-createlanguage">
19 <primary>CREATE LANGUAGE</primary>
24 CREATE [ OR REPLACE ] [ PROCEDURAL ] LANGUAGE <replaceable class="parameter">name</replaceable>
25 CREATE [ OR REPLACE ] [ TRUSTED ] [ PROCEDURAL ] LANGUAGE <replaceable class="parameter">name</replaceable>
26 HANDLER <replaceable class="parameter">call_handler</replaceable> [ INLINE <replaceable class="parameter">inline_handler</replaceable> ] [ VALIDATOR <replaceable>valfunction</replaceable> ]
30 <refsect1 id="sql-createlanguage-description">
31 <title>Description</title>
34 <command>CREATE LANGUAGE</command> registers a new
35 procedural language with a <productname>PostgreSQL</productname>
36 database. Subsequently, functions and trigger procedures can be
37 defined in this new language.
42 As of <productname>PostgreSQL</productname> 9.1, most procedural
43 languages have been made into <quote>extensions</>, and should
44 therefore be installed with <xref linkend="sql-createextension">
45 not <command>CREATE LANGUAGE</command>. Direct use of
46 <command>CREATE LANGUAGE</command> should now be confined to
47 extension installation scripts. If you have a <quote>bare</>
48 language in your database, perhaps as a result of an upgrade,
49 you can convert it to an extension using
50 <literal>CREATE EXTENSION <replaceable>langname</> FROM
56 <command>CREATE LANGUAGE</command> effectively associates the
57 language name with handler function(s) that are responsible for executing
58 functions written in the language. Refer to <xref linkend="plhandler">
59 for more information about language handlers.
63 There are two forms of the <command>CREATE LANGUAGE</command> command.
64 In the first form, the user supplies just the name of the desired
65 language, and the <productname>PostgreSQL</productname> server consults
66 the <link linkend="catalog-pg-pltemplate"><structname>pg_pltemplate</structname></link>
67 system catalog to determine the correct parameters. In the second form,
68 the user supplies the language parameters along with the language name.
69 The second form can be used to create a language that is not defined in
70 <structname>pg_pltemplate</>, but this approach is considered obsolescent.
74 When the server finds an entry in the <structname>pg_pltemplate</> catalog
75 for the given language name, it will use the catalog data even if the
76 command includes language parameters. This behavior simplifies loading of
77 old dump files, which are likely to contain out-of-date information
78 about language support functions.
82 Ordinarily, the user must have the
83 <productname>PostgreSQL</productname> superuser privilege to
84 register a new language. However, the owner of a database can register
85 a new language within that database if the language is listed in
86 the <structname>pg_pltemplate</structname> catalog and is marked
87 as allowed to be created by database owners (<structfield>tmpldbacreate</>
88 is true). The default is that trusted languages can be created
89 by database owners, but this can be adjusted by superusers by modifying
90 the contents of <structname>pg_pltemplate</structname>.
91 The creator of a language becomes its owner and can later
92 drop it, rename it, or assign it to a new owner.
96 <command>CREATE OR REPLACE LANGUAGE</command> will either create a
97 new language, or replace an existing definition. If the language
98 already exists, its parameters are updated according to the values
99 specified or taken from <structname>pg_pltemplate</structname>,
100 but the language's ownership and permissions settings do not change,
101 and any existing functions written in the language are assumed to still
102 be valid. In addition to the normal privilege requirements for creating
103 a language, the user must be superuser or owner of the existing language.
104 The <literal>REPLACE</> case is mainly meant to be used to
105 ensure that the language exists. If the language has a
106 <structname>pg_pltemplate</structname> entry then <literal>REPLACE</>
107 will not actually change anything about an existing definition, except in
108 the unusual case where the <structname>pg_pltemplate</structname> entry
109 has been modified since the language was created.
113 <refsect1 id="sql-createlanguage-parameters">
114 <title>Parameters</title>
118 <term><literal>TRUSTED</literal></term>
121 <para><literal>TRUSTED</literal> specifies that the language does
122 not grant access to data that the user would not otherwise
123 have. If this key word is omitted
124 when registering the language, only users with the
125 <productname>PostgreSQL</productname> superuser privilege can
126 use this language to create new functions.
132 <term><literal>PROCEDURAL</literal></term>
136 This is a noise word.
142 <term><replaceable class="parameter">name</replaceable></term>
146 The name of the new procedural language.
147 The name must be unique among the languages in the database.
151 For backward compatibility, the name can be enclosed by single
158 <term><literal>HANDLER</literal> <replaceable class="parameter">call_handler</replaceable></term>
161 <para><replaceable class="parameter">call_handler</replaceable> is
162 the name of a previously registered function that will be
163 called to execute the procedural language's functions. The call
164 handler for a procedural language must be written in a compiled
165 language such as C with version 1 call convention and
166 registered with <productname>PostgreSQL</productname> as a
167 function taking no arguments and returning the
168 <type>language_handler</type> type, a placeholder type that is
169 simply used to identify the function as a call handler.
175 <term><literal>INLINE</literal> <replaceable class="parameter">inline_handler</replaceable></term>
178 <para><replaceable class="parameter">inline_handler</replaceable> is the
179 name of a previously registered function that will be called
180 to execute an anonymous code block
181 (<xref linkend="sql-do"> command)
183 If no <replaceable class="parameter">inline_handler</replaceable>
184 function is specified, the language does not support anonymous code
186 The handler function must take one argument of
187 type <type>internal</type>, which will be the <command>DO</> command's
188 internal representation, and it will typically return
189 <type>void</>. The return value of the handler is ignored.
195 <term><literal>VALIDATOR</literal> <replaceable class="parameter">valfunction</replaceable></term>
198 <para><replaceable class="parameter">valfunction</replaceable> is the
199 name of a previously registered function that will be called
200 when a new function in the language is created, to validate the
203 validator function is specified, then a new function will not
204 be checked when it is created.
205 The validator function must take one argument of
206 type <type>oid</type>, which will be the OID of the
207 to-be-created function, and will typically return <type>void</>.
211 A validator function would typically inspect the function body
212 for syntactical correctness, but it can also look at other
213 properties of the function, for example if the language cannot
214 handle certain argument types. To signal an error, the
215 validator function should use the <function>ereport()</function>
216 function. The return value of the function is ignored.
223 The <literal>TRUSTED</> option and the support function name(s) are
224 ignored if the server has an entry for the specified language
225 name in <structname>pg_pltemplate</>.
229 <refsect1 id="sql-createlanguage-notes">
233 The <xref linkend="app-createlang"> program is a simple wrapper around
234 the <command>CREATE LANGUAGE</> command. It eases
235 installation of procedural languages from the shell command line.
239 Use <xref linkend="sql-droplanguage">, or better yet the <xref
240 linkend="app-droplang"> program, to drop procedural languages.
244 The system catalog <classname>pg_language</classname> (see <xref
245 linkend="catalog-pg-language">) records information about the
246 currently installed languages. Also, <command>createlang</command>
247 has an option to list the installed languages.
251 To create functions in a procedural language, a user must have the
252 <literal>USAGE</literal> privilege for the language. By default,
253 <literal>USAGE</> is granted to <literal>PUBLIC</> (i.e., everyone)
254 for trusted languages. This can be revoked if desired.
258 Procedural languages are local to individual databases.
259 However, a language can be installed into the <literal>template1</literal>
260 database, which will cause it to be available automatically in
261 all subsequently-created databases.
265 The call handler function, the inline handler function (if any),
266 and the validator function (if any)
267 must already exist if the server does not have an entry for the language
268 in <structname>pg_pltemplate</>. But when there is an entry,
269 the functions need not already exist;
270 they will be automatically defined if not present in the database.
271 (This might result in <command>CREATE LANGUAGE</> failing, if the
272 shared library that implements the language is not available in
277 In <productname>PostgreSQL</productname> versions before 7.3, it was
278 necessary to declare handler functions as returning the placeholder
279 type <type>opaque</>, rather than <type>language_handler</>.
281 of old dump files, <command>CREATE LANGUAGE</> will accept a function
282 declared as returning <type>opaque</>, but it will issue a notice and
283 change the function's declared return type to <type>language_handler</>.
287 <refsect1 id="sql-createlanguage-examples">
288 <title>Examples</title>
291 The preferred way of creating any of the standard procedural languages
294 CREATE LANGUAGE plperl;
299 For a language not known in the <structname>pg_pltemplate</> catalog, a
300 sequence such as this is needed:
302 CREATE FUNCTION plsample_call_handler() RETURNS language_handler
303 AS '$libdir/plsample'
305 CREATE LANGUAGE plsample
306 HANDLER plsample_call_handler;
307 </programlisting></para>
310 <refsect1 id="sql-createlanguage-compat">
311 <title>Compatibility</title>
314 <command>CREATE LANGUAGE</command> is a
315 <productname>PostgreSQL</productname> extension.
320 <title>See Also</title>
322 <simplelist type="inline">
323 <member><xref linkend="sql-alterlanguage"></member>
324 <member><xref linkend="sql-createfunction"></member>
325 <member><xref linkend="sql-droplanguage"></member>
326 <member><xref linkend="sql-grant"></member>
327 <member><xref linkend="sql-revoke"></member>
328 <member><xref linkend="app-createlang"></member>
329 <member><xref linkend="app-droplang"></member>