1<!-- doc/src/sgml/extend.sgml --> 2 3 <chapter id="extend"> 4 <title>Extending <acronym>SQL</acronym></title> 5 6 <indexterm zone="extend"> 7 <primary>extending SQL</primary> 8 </indexterm> 9 10 <para> 11 In the sections that follow, we will discuss how you 12 can extend the <productname>PostgreSQL</productname> 13 <acronym>SQL</acronym> query language by adding: 14 15 <itemizedlist spacing="compact" mark="bullet"> 16 <listitem> 17 <para> 18 functions (starting in <xref linkend="xfunc"/>) 19 </para> 20 </listitem> 21 <listitem> 22 <para> 23 aggregates (starting in <xref linkend="xaggr"/>) 24 </para> 25 </listitem> 26 <listitem> 27 <para> 28 data types (starting in <xref linkend="xtypes"/>) 29 </para> 30 </listitem> 31 <listitem> 32 <para> 33 operators (starting in <xref linkend="xoper"/>) 34 </para> 35 </listitem> 36 <listitem> 37 <para> 38 operator classes for indexes (starting in <xref linkend="xindex"/>) 39 </para> 40 </listitem> 41 <listitem> 42 <para> 43 packages of related objects (starting in <xref linkend="extend-extensions"/>) 44 </para> 45 </listitem> 46 </itemizedlist> 47 </para> 48 49 <sect1 id="extend-how"> 50 <title>How Extensibility Works</title> 51 52 <para> 53 <productname>PostgreSQL</productname> is extensible because its operation is 54 catalog-driven. If you are familiar with standard 55 relational database systems, you know that they store information 56 about databases, tables, columns, etc., in what are 57 commonly known as system catalogs. (Some systems call 58 this the data dictionary.) The catalogs appear to the 59 user as tables like any other, but the <acronym>DBMS</acronym> stores 60 its internal bookkeeping in them. One key difference 61 between <productname>PostgreSQL</productname> and standard relational database systems is 62 that <productname>PostgreSQL</productname> stores much more information in its 63 catalogs: not only information about tables and columns, 64 but also information about data types, functions, access 65 methods, and so on. These tables can be modified by 66 the user, and since <productname>PostgreSQL</productname> bases its operation 67 on these tables, this means that <productname>PostgreSQL</productname> can be 68 extended by users. By comparison, conventional 69 database systems can only be extended by changing hardcoded 70 procedures in the source code or by loading modules 71 specially written by the <acronym>DBMS</acronym> vendor. 72 </para> 73 74 <para> 75 The <productname>PostgreSQL</productname> server can moreover 76 incorporate user-written code into itself through dynamic loading. 77 That is, the user can specify an object code file (e.g., a shared 78 library) that implements a new type or function, and 79 <productname>PostgreSQL</productname> will load it as required. 80 Code written in <acronym>SQL</acronym> is even more trivial to add 81 to the server. This ability to modify its operation <quote>on the 82 fly</quote> makes <productname>PostgreSQL</productname> uniquely 83 suited for rapid prototyping of new applications and storage 84 structures. 85 </para> 86 </sect1> 87 88 <sect1 id="extend-type-system"> 89 <title>The <productname>PostgreSQL</productname> Type System</title> 90 91 <indexterm zone="extend-type-system"> 92 <primary>base type</primary> 93 </indexterm> 94 95 <indexterm zone="extend-type-system"> 96 <primary>data type</primary> 97 <secondary>base</secondary> 98 </indexterm> 99 100 <indexterm zone="extend-type-system"> 101 <primary>composite type</primary> 102 </indexterm> 103 104 <indexterm zone="extend-type-system"> 105 <primary>data type</primary> 106 <secondary>composite</secondary> 107 </indexterm> 108 109 <indexterm zone="extend-type-system"> 110 <primary>container type</primary> 111 </indexterm> 112 113 <indexterm zone="extend-type-system"> 114 <primary>data type</primary> 115 <secondary>container</secondary> 116 </indexterm> 117 118 <para> 119 <productname>PostgreSQL</productname> data types can be divided into base 120 types, container types, domains, and pseudo-types. 121 </para> 122 123 <sect2> 124 <title>Base Types</title> 125 126 <para> 127 Base types are those, like <type>integer</type>, that are 128 implemented below the level of the <acronym>SQL</acronym> language 129 (typically in a low-level language such as C). They generally 130 correspond to what are often known as abstract data types. 131 <productname>PostgreSQL</productname> can only operate on such 132 types through functions provided by the user and only understands 133 the behavior of such types to the extent that the user describes 134 them. 135 The built-in base types are described in <xref linkend="datatype"/>. 136 </para> 137 138 <para> 139 Enumerated (enum) types can be considered as a subcategory of base 140 types. The main difference is that they can be created using 141 just <acronym>SQL</acronym> commands, without any low-level programming. 142 Refer to <xref linkend="datatype-enum"/> for more information. 143 </para> 144 </sect2> 145 146 <sect2> 147 <title>Container Types</title> 148 149 <para> 150 <productname>PostgreSQL</productname> has three kinds 151 of <quote>container</quote> types, which are types that contain multiple 152 values of other types. These are arrays, composites, and ranges. 153 </para> 154 155 <para> 156 Arrays can hold multiple values that are all of the same type. An array 157 type is automatically created for each base type, composite type, range 158 type, and domain type. But there are no arrays of arrays. So far as 159 the type system is concerned, multi-dimensional arrays are the same as 160 one-dimensional arrays. Refer to <xref linkend="arrays"/> for more 161 information. 162 </para> 163 164 <para> 165 Composite types, or row types, are created whenever the user 166 creates a table. It is also possible to use <xref 167 linkend="sql-createtype"/> to 168 define a <quote>stand-alone</quote> composite type with no associated 169 table. A composite type is simply a list of types with 170 associated field names. A value of a composite type is a row or 171 record of field values. Refer to <xref linkend="rowtypes"/> 172 for more information. 173 </para> 174 175 <para> 176 A range type can hold two values of the same type, which are the lower 177 and upper bounds of the range. Range types are user-created, although 178 a few built-in ones exist. Refer to <xref linkend="rangetypes"/> 179 for more information. 180 </para> 181 </sect2> 182 183 <sect2 id="extend-type-system-domains"> 184 <title>Domains</title> 185 186 <para> 187 A domain is based on a particular underlying type and for many purposes 188 is interchangeable with its underlying type. However, a domain can have 189 constraints that restrict its valid values to a subset of what the 190 underlying type would allow. Domains are created using 191 the <acronym>SQL</acronym> command <xref linkend="sql-createdomain"/>. 192 Refer to <xref linkend="domains"/> for more information. 193 </para> 194 </sect2> 195 196 <sect2> 197 <title>Pseudo-Types</title> 198 199 <para> 200 There are a few <quote>pseudo-types</quote> for special purposes. 201 Pseudo-types cannot appear as columns of tables or components of 202 container types, but they can be used to declare the argument and 203 result types of functions. This provides a mechanism within the 204 type system to identify special classes of functions. <xref 205 linkend="datatype-pseudotypes-table"/> lists the existing 206 pseudo-types. 207 </para> 208 </sect2> 209 210 <sect2 id="extend-types-polymorphic"> 211 <title>Polymorphic Types</title> 212 213 <indexterm zone="extend-types-polymorphic"> 214 <primary>polymorphic type</primary> 215 </indexterm> 216 217 <indexterm zone="extend-types-polymorphic"> 218 <primary>polymorphic function</primary> 219 </indexterm> 220 221 <indexterm zone="extend-types-polymorphic"> 222 <primary>data type</primary> 223 <secondary>polymorphic</secondary> 224 </indexterm> 225 226 <indexterm zone="extend-types-polymorphic"> 227 <primary>function</primary> 228 <secondary>polymorphic</secondary> 229 </indexterm> 230 231 <para> 232 Some pseudo-types of special interest are the <firstterm>polymorphic 233 types</firstterm>, which are used to declare <firstterm>polymorphic 234 functions</firstterm>. This powerful feature allows a single function 235 definition to operate on many different data types, with the specific 236 data type(s) being determined by the data types actually passed to it 237 in a particular call. The polymorphic types are shown in 238 <xref linkend="extend-types-polymorphic-table"/>. Some examples of 239 their use appear in <xref linkend="xfunc-sql-polymorphic-functions"/>. 240 </para> 241 242 <table id="extend-types-polymorphic-table"> 243 <title>Polymorphic Types</title> 244 <tgroup cols="3"> 245 <colspec colname="col1" colwidth="2*"/> 246 <colspec colname="col2" colwidth="1*"/> 247 <colspec colname="col3" colwidth="2*"/> 248 <thead> 249 <row> 250 <entry>Name</entry> 251 <entry>Family</entry> 252 <entry>Description</entry> 253 </row> 254 </thead> 255 256 <tbody> 257 <row> 258 <entry><type>anyelement</type></entry> 259 <entry>Simple</entry> 260 <entry>Indicates that a function accepts any data type</entry> 261 </row> 262 263 <row> 264 <entry><type>anyarray</type></entry> 265 <entry>Simple</entry> 266 <entry>Indicates that a function accepts any array data type</entry> 267 </row> 268 269 <row> 270 <entry><type>anynonarray</type></entry> 271 <entry>Simple</entry> 272 <entry>Indicates that a function accepts any non-array data type</entry> 273 </row> 274 275 <row> 276 <entry><type>anyenum</type></entry> 277 <entry>Simple</entry> 278 <entry>Indicates that a function accepts any enum data type 279 (see <xref linkend="datatype-enum"/>) 280 </entry> 281 </row> 282 283 <row> 284 <entry><type>anyrange</type></entry> 285 <entry>Simple</entry> 286 <entry>Indicates that a function accepts any range data type 287 (see <xref linkend="rangetypes"/>) 288 </entry> 289 </row> 290 291 <row> 292 <entry><type>anycompatible</type></entry> 293 <entry>Common</entry> 294 <entry>Indicates that a function accepts any data type, 295 with automatic promotion of multiple arguments to a common data type 296 </entry> 297 </row> 298 299 <row> 300 <entry><type>anycompatiblearray</type></entry> 301 <entry>Common</entry> 302 <entry>Indicates that a function accepts any array data type, 303 with automatic promotion of multiple arguments to a common data type 304 </entry> 305 </row> 306 307 <row> 308 <entry><type>anycompatiblenonarray</type></entry> 309 <entry>Common</entry> 310 <entry>Indicates that a function accepts any non-array data type, 311 with automatic promotion of multiple arguments to a common data type 312 </entry> 313 </row> 314 315 <row> 316 <entry><type>anycompatiblerange</type></entry> 317 <entry>Common</entry> 318 <entry>Indicates that a function accepts any range data type, 319 with automatic promotion of multiple arguments to a common data type 320 </entry> 321 </row> 322 </tbody> 323 </tgroup> 324 </table> 325 326 <para> 327 Polymorphic arguments and results are tied to each other and are resolved 328 to specific data types when a query calling a polymorphic function is 329 parsed. When there is more than one polymorphic argument, the actual 330 data types of the input values must match up as described below. If the 331 function's result type is polymorphic, or it has output parameters of 332 polymorphic types, the types of those results are deduced from the 333 actual types of the polymorphic inputs as described below. 334 </para> 335 336 <para> 337 For the <quote>simple</quote> family of polymorphic types, the 338 matching and deduction rules work like this: 339 </para> 340 341 <para> 342 Each position (either argument or return value) declared as 343 <type>anyelement</type> is allowed to have any specific actual 344 data type, but in any given call they must all be the 345 <emphasis>same</emphasis> actual type. Each 346 position declared as <type>anyarray</type> can have any array data type, 347 but similarly they must all be the same type. And similarly, 348 positions declared as <type>anyrange</type> must all be the same range 349 type. Furthermore, if there are 350 positions declared <type>anyarray</type> and others declared 351 <type>anyelement</type>, the actual array type in the 352 <type>anyarray</type> positions must be an array whose elements are 353 the same type appearing in the <type>anyelement</type> positions. 354 Similarly, if there are positions declared <type>anyrange</type> 355 and others declared <type>anyelement</type> or <type>anyarray</type>, 356 the actual range type in the <type>anyrange</type> positions must be a 357 range whose subtype is the same type appearing in 358 the <type>anyelement</type> positions and the same as the element type 359 of the <type>anyarray</type> positions. 360 <type>anynonarray</type> is treated exactly the same as <type>anyelement</type>, 361 but adds the additional constraint that the actual type must not be 362 an array type. 363 <type>anyenum</type> is treated exactly the same as <type>anyelement</type>, 364 but adds the additional constraint that the actual type must 365 be an enum type. 366 </para> 367 368 <para> 369 Thus, when more than one argument position is declared with a polymorphic 370 type, the net effect is that only certain combinations of actual argument 371 types are allowed. For example, a function declared as 372 <literal>equal(anyelement, anyelement)</literal> will take any two input values, 373 so long as they are of the same data type. 374 </para> 375 376 <para> 377 When the return value of a function is declared as a polymorphic type, 378 there must be at least one argument position that is also polymorphic, 379 and the actual data type(s) supplied for the polymorphic arguments 380 determine the actual 381 result type for that call. For example, if there were not already 382 an array subscripting mechanism, one could define a function that 383 implements subscripting as <literal>subscript(anyarray, integer) 384 returns anyelement</literal>. This declaration constrains the actual first 385 argument to be an array type, and allows the parser to infer the correct 386 result type from the actual first argument's type. Another example 387 is that a function declared as <literal>f(anyarray) returns anyenum</literal> 388 will only accept arrays of enum types. 389 </para> 390 391 <para> 392 In most cases, the parser can infer the actual data type for a 393 polymorphic result type from arguments that are of a different 394 polymorphic type in the same family; for example <type>anyarray</type> 395 can be deduced from <type>anyelement</type> or vice versa. 396 An exception is that a 397 polymorphic result of type <type>anyrange</type> requires an argument 398 of type <type>anyrange</type>; it cannot be deduced 399 from <type>anyarray</type> or <type>anyelement</type> arguments. This 400 is because there could be multiple range types with the same subtype. 401 </para> 402 403 <para> 404 Note that <type>anynonarray</type> and <type>anyenum</type> do not represent 405 separate type variables; they are the same type as 406 <type>anyelement</type>, just with an additional constraint. For 407 example, declaring a function as <literal>f(anyelement, anyenum)</literal> 408 is equivalent to declaring it as <literal>f(anyenum, anyenum)</literal>: 409 both actual arguments have to be the same enum type. 410 </para> 411 412 <para> 413 For the <quote>common</quote> family of polymorphic types, the 414 matching and deduction rules work approximately the same as for 415 the <quote>simple</quote> family, with one major difference: the 416 actual types of the arguments need not be identical, so long as they 417 can be implicitly cast to a single common type. The common type is 418 selected following the same rules as for <literal>UNION</literal> and 419 related constructs (see <xref linkend="typeconv-union-case"/>). 420 Selection of the common type considers the actual types 421 of <type>anycompatible</type> and <type>anycompatiblenonarray</type> 422 inputs, the array element types of <type>anycompatiblearray</type> 423 inputs, and the range subtypes of <type>anycompatiblerange</type> 424 inputs. If <type>anycompatiblenonarray</type> is present then the 425 common type is required to be a non-array type. Once a common type is 426 identified, arguments in <type>anycompatible</type> 427 and <type>anycompatiblenonarray</type> positions are automatically 428 cast to that type, and arguments in <type>anycompatiblearray</type> 429 positions are automatically cast to the array type for that type. 430 </para> 431 432 <para> 433 Since there is no way to select a range type knowing only its subtype, 434 use of <type>anycompatiblerange</type> requires that all arguments 435 declared with that type have the same actual range type, and that that 436 type's subtype agree with the selected common type, so that no casting 437 of the range values is required. As with <type>anyrange</type>, use 438 of <type>anycompatiblerange</type> as a function result type requires 439 that there be an <type>anycompatiblerange</type> argument. 440 </para> 441 442 <para> 443 Notice that there is no <type>anycompatibleenum</type> type. Such a 444 type would not be very useful, since there normally are not any 445 implicit casts to enum types, meaning that there would be no way to 446 resolve a common type for dissimilar enum inputs. 447 </para> 448 449 <para> 450 The <quote>simple</quote> and <quote>common</quote> polymorphic 451 families represent two independent sets of type variables. Consider 452 for example 453<programlisting> 454CREATE FUNCTION myfunc(a anyelement, b anyelement, 455 c anycompatible, d anycompatible) 456RETURNS anycompatible AS ... 457</programlisting> 458 In an actual call of this function, the first two inputs must have 459 exactly the same type. The last two inputs must be promotable to a 460 common type, but this type need not have anything to do with the type 461 of the first two inputs. The result will have the common type of the 462 last two inputs. 463 </para> 464 465 <para> 466 A variadic function (one taking a variable number of arguments, as in 467 <xref linkend="xfunc-sql-variadic-functions"/>) can be 468 polymorphic: this is accomplished by declaring its last parameter as 469 <literal>VARIADIC</literal> <type>anyarray</type> or 470 <literal>VARIADIC</literal> <type>anycompatiblearray</type>. 471 For purposes of argument 472 matching and determining the actual result type, such a function behaves 473 the same as if you had written the appropriate number of 474 <type>anynonarray</type> or <type>anycompatiblenonarray</type> 475 parameters. 476 </para> 477 </sect2> 478 </sect1> 479 480 &xfunc; 481 &xaggr; 482 &xtypes; 483 &xoper; 484 &xindex; 485 486 487 <sect1 id="extend-extensions"> 488 <title>Packaging Related Objects into an Extension</title> 489 490 <indexterm zone="extend-extensions"> 491 <primary>extension</primary> 492 </indexterm> 493 494 <para> 495 A useful extension to <productname>PostgreSQL</productname> typically includes 496 multiple SQL objects; for example, a new data type will require new 497 functions, new operators, and probably new index operator classes. 498 It is helpful to collect all these objects into a single package 499 to simplify database management. <productname>PostgreSQL</productname> calls 500 such a package an <firstterm>extension</firstterm>. To define an extension, 501 you need at least a <firstterm>script file</firstterm> that contains the 502 <acronym>SQL</acronym> commands to create the extension's objects, and a 503 <firstterm>control file</firstterm> that specifies a few basic properties 504 of the extension itself. If the extension includes C code, there 505 will typically also be a shared library file into which the C code 506 has been built. Once you have these files, a simple 507 <xref linkend="sql-createextension"/> command loads the objects into 508 your database. 509 </para> 510 511 <para> 512 The main advantage of using an extension, rather than just running the 513 <acronym>SQL</acronym> script to load a bunch of <quote>loose</quote> objects 514 into your database, is that <productname>PostgreSQL</productname> will then 515 understand that the objects of the extension go together. You can 516 drop all the objects with a single <xref linkend="sql-dropextension"/> 517 command (no need to maintain a separate <quote>uninstall</quote> script). 518 Even more useful, <application>pg_dump</application> knows that it should not 519 dump the individual member objects of the extension — it will 520 just include a <command>CREATE EXTENSION</command> command in dumps, instead. 521 This vastly simplifies migration to a new version of the extension 522 that might contain more or different objects than the old version. 523 Note however that you must have the extension's control, script, and 524 other files available when loading such a dump into a new database. 525 </para> 526 527 <para> 528 <productname>PostgreSQL</productname> will not let you drop an individual object 529 contained in an extension, except by dropping the whole extension. 530 Also, while you can change the definition of an extension member object 531 (for example, via <command>CREATE OR REPLACE FUNCTION</command> for a 532 function), bear in mind that the modified definition will not be dumped 533 by <application>pg_dump</application>. Such a change is usually only sensible if 534 you concurrently make the same change in the extension's script file. 535 (But there are special provisions for tables containing configuration 536 data; see <xref linkend="extend-extensions-config-tables"/>.) 537 In production situations, it's generally better to create an extension 538 update script to perform changes to extension member objects. 539 </para> 540 541 <para> 542 The extension script may set privileges on objects that are part of the 543 extension, using <command>GRANT</command> and <command>REVOKE</command> 544 statements. The final set of privileges for each object (if any are set) 545 will be stored in the 546 <link linkend="catalog-pg-init-privs"><structname>pg_init_privs</structname></link> 547 system catalog. When <application>pg_dump</application> is used, the 548 <command>CREATE EXTENSION</command> command will be included in the dump, followed 549 by the set of <command>GRANT</command> and <command>REVOKE</command> 550 statements necessary to set the privileges on the objects to what they were 551 at the time the dump was taken. 552 </para> 553 554 <para> 555 <productname>PostgreSQL</productname> does not currently support extension scripts 556 issuing <command>CREATE POLICY</command> or <command>SECURITY LABEL</command> 557 statements. These are expected to be set after the extension has been 558 created. All RLS policies and security labels on extension objects will be 559 included in dumps created by <application>pg_dump</application>. 560 </para> 561 562 <para> 563 The extension mechanism also has provisions for packaging modification 564 scripts that adjust the definitions of the SQL objects contained in an 565 extension. For example, if version 1.1 of an extension adds one function 566 and changes the body of another function compared to 1.0, the extension 567 author can provide an <firstterm>update script</firstterm> that makes just those 568 two changes. The <command>ALTER EXTENSION UPDATE</command> command can then 569 be used to apply these changes and track which version of the extension 570 is actually installed in a given database. 571 </para> 572 573 <para> 574 The kinds of SQL objects that can be members of an extension are shown in 575 the description of <xref linkend="sql-alterextension"/>. Notably, objects 576 that are database-cluster-wide, such as databases, roles, and tablespaces, 577 cannot be extension members since an extension is only known within one 578 database. (Although an extension script is not prohibited from creating 579 such objects, if it does so they will not be tracked as part of the 580 extension.) Also notice that while a table can be a member of an 581 extension, its subsidiary objects such as indexes are not directly 582 considered members of the extension. 583 Another important point is that schemas can belong to extensions, but not 584 vice versa: an extension as such has an unqualified name and does not 585 exist <quote>within</quote> any schema. The extension's member objects, 586 however, will belong to schemas whenever appropriate for their object 587 types. It may or may not be appropriate for an extension to own the 588 schema(s) its member objects are within. 589 </para> 590 591 <para> 592 If an extension's script creates any temporary objects (such as temp 593 tables), those objects are treated as extension members for the 594 remainder of the current session, but are automatically dropped at 595 session end, as any temporary object would be. This is an exception 596 to the rule that extension member objects cannot be dropped without 597 dropping the whole extension. 598 </para> 599 600 <sect2> 601 <title>Extension Files</title> 602 603 <indexterm> 604 <primary>control file</primary> 605 </indexterm> 606 607 <para> 608 The <xref linkend="sql-createextension"/> command relies on a control 609 file for each extension, which must be named the same as the extension 610 with a suffix of <literal>.control</literal>, and must be placed in the 611 installation's <literal>SHAREDIR/extension</literal> directory. There 612 must also be at least one <acronym>SQL</acronym> script file, which follows the 613 naming pattern 614 <literal><replaceable>extension</replaceable>--<replaceable>version</replaceable>.sql</literal> 615 (for example, <literal>foo--1.0.sql</literal> for version <literal>1.0</literal> of 616 extension <literal>foo</literal>). By default, the script file(s) are also 617 placed in the <literal>SHAREDIR/extension</literal> directory; but the 618 control file can specify a different directory for the script file(s). 619 </para> 620 621 <para> 622 The file format for an extension control file is the same as for the 623 <filename>postgresql.conf</filename> file, namely a list of 624 <replaceable>parameter_name</replaceable> <literal>=</literal> <replaceable>value</replaceable> 625 assignments, one per line. Blank lines and comments introduced by 626 <literal>#</literal> are allowed. Be sure to quote any value that is not 627 a single word or number. 628 </para> 629 630 <para> 631 A control file can set the following parameters: 632 </para> 633 634 <variablelist> 635 <varlistentry> 636 <term><varname>directory</varname> (<type>string</type>)</term> 637 <listitem> 638 <para> 639 The directory containing the extension's <acronym>SQL</acronym> script 640 file(s). Unless an absolute path is given, the name is relative to 641 the installation's <literal>SHAREDIR</literal> directory. The 642 default behavior is equivalent to specifying 643 <literal>directory = 'extension'</literal>. 644 </para> 645 </listitem> 646 </varlistentry> 647 648 <varlistentry> 649 <term><varname>default_version</varname> (<type>string</type>)</term> 650 <listitem> 651 <para> 652 The default version of the extension (the one that will be installed 653 if no version is specified in <command>CREATE EXTENSION</command>). Although 654 this can be omitted, that will result in <command>CREATE EXTENSION</command> 655 failing if no <literal>VERSION</literal> option appears, so you generally 656 don't want to do that. 657 </para> 658 </listitem> 659 </varlistentry> 660 661 <varlistentry> 662 <term><varname>comment</varname> (<type>string</type>)</term> 663 <listitem> 664 <para> 665 A comment (any string) about the extension. The comment is applied 666 when initially creating an extension, but not during extension updates 667 (since that might override user-added comments). Alternatively, 668 the extension's comment can be set by writing 669 a <xref linkend="sql-comment"/> command in the script file. 670 </para> 671 </listitem> 672 </varlistentry> 673 674 <varlistentry> 675 <term><varname>encoding</varname> (<type>string</type>)</term> 676 <listitem> 677 <para> 678 The character set encoding used by the script file(s). This should 679 be specified if the script files contain any non-ASCII characters. 680 Otherwise the files will be assumed to be in the database encoding. 681 </para> 682 </listitem> 683 </varlistentry> 684 685 <varlistentry> 686 <term><varname>module_pathname</varname> (<type>string</type>)</term> 687 <listitem> 688 <para> 689 The value of this parameter will be substituted for each occurrence 690 of <literal>MODULE_PATHNAME</literal> in the script file(s). If it is not 691 set, no substitution is made. Typically, this is set to 692 <literal>$libdir/<replaceable>shared_library_name</replaceable></literal> and 693 then <literal>MODULE_PATHNAME</literal> is used in <command>CREATE 694 FUNCTION</command> commands for C-language functions, so that the script 695 files do not need to hard-wire the name of the shared library. 696 </para> 697 </listitem> 698 </varlistentry> 699 700 <varlistentry> 701 <term><varname>requires</varname> (<type>string</type>)</term> 702 <listitem> 703 <para> 704 A list of names of extensions that this extension depends on, 705 for example <literal>requires = 'foo, bar'</literal>. Those 706 extensions must be installed before this one can be installed. 707 </para> 708 </listitem> 709 </varlistentry> 710 711 <varlistentry> 712 <term><varname>superuser</varname> (<type>boolean</type>)</term> 713 <listitem> 714 <para> 715 If this parameter is <literal>true</literal> (which is the default), 716 only superusers can create the extension or update it to a new 717 version (but see also <varname>trusted</varname>, below). 718 If it is set to <literal>false</literal>, just the privileges 719 required to execute the commands in the installation or update script 720 are required. 721 This should normally be set to <literal>true</literal> if any of the 722 script commands require superuser privileges. (Such commands would 723 fail anyway, but it's more user-friendly to give the error up front.) 724 </para> 725 </listitem> 726 </varlistentry> 727 728 <varlistentry> 729 <term><varname>trusted</varname> (<type>boolean</type>)</term> 730 <listitem> 731 <para> 732 This parameter, if set to <literal>true</literal> (which is not the 733 default), allows some non-superusers to install an extension that 734 has <varname>superuser</varname> set to <literal>true</literal>. 735 Specifically, installation will be permitted for anyone who has 736 <literal>CREATE</literal> privilege on the current database. 737 When the user executing <command>CREATE EXTENSION</command> is not 738 a superuser but is allowed to install by virtue of this parameter, 739 then the installation or update script is run as the bootstrap 740 superuser, not as the calling user. 741 This parameter is irrelevant if <varname>superuser</varname> is 742 <literal>false</literal>. 743 Generally, this should not be set true for extensions that could 744 allow access to otherwise-superuser-only abilities, such as 745 file system access. 746 Also, marking an extension trusted requires significant extra effort 747 to write the extension's installation and update script(s) securely; 748 see <xref linkend="extend-extensions-security"/>. 749 </para> 750 </listitem> 751 </varlistentry> 752 753 <varlistentry> 754 <term><varname>relocatable</varname> (<type>boolean</type>)</term> 755 <listitem> 756 <para> 757 An extension is <firstterm>relocatable</firstterm> if it is possible to move 758 its contained objects into a different schema after initial creation 759 of the extension. The default is <literal>false</literal>, i.e., the 760 extension is not relocatable. 761 See <xref linkend="extend-extensions-relocation"/> for more information. 762 </para> 763 </listitem> 764 </varlistentry> 765 766 <varlistentry> 767 <term><varname>schema</varname> (<type>string</type>)</term> 768 <listitem> 769 <para> 770 This parameter can only be set for non-relocatable extensions. 771 It forces the extension to be loaded into exactly the named schema 772 and not any other. 773 The <varname>schema</varname> parameter is consulted only when 774 initially creating an extension, not during extension updates. 775 See <xref linkend="extend-extensions-relocation"/> for more information. 776 </para> 777 </listitem> 778 </varlistentry> 779 </variablelist> 780 781 <para> 782 In addition to the primary control file 783 <literal><replaceable>extension</replaceable>.control</literal>, 784 an extension can have secondary control files named in the style 785 <literal><replaceable>extension</replaceable>--<replaceable>version</replaceable>.control</literal>. 786 If supplied, these must be located in the script file directory. 787 Secondary control files follow the same format as the primary control 788 file. Any parameters set in a secondary control file override the 789 primary control file when installing or updating to that version of 790 the extension. However, the parameters <varname>directory</varname> and 791 <varname>default_version</varname> cannot be set in a secondary control file. 792 </para> 793 794 <para> 795 An extension's <acronym>SQL</acronym> script files can contain any SQL commands, 796 except for transaction control commands (<command>BEGIN</command>, 797 <command>COMMIT</command>, etc) and commands that cannot be executed inside a 798 transaction block (such as <command>VACUUM</command>). This is because the 799 script files are implicitly executed within a transaction block. 800 </para> 801 802 <para> 803 An extension's <acronym>SQL</acronym> script files can also contain lines 804 beginning with <literal>\echo</literal>, which will be ignored (treated as 805 comments) by the extension mechanism. This provision is commonly used 806 to throw an error if the script file is fed to <application>psql</application> 807 rather than being loaded via <command>CREATE EXTENSION</command> (see example 808 script in <xref linkend="extend-extensions-example"/>). 809 Without that, users might accidentally load the 810 extension's contents as <quote>loose</quote> objects rather than as an 811 extension, a state of affairs that's a bit tedious to recover from. 812 </para> 813 814 <para> 815 If the extension script contains the 816 string <literal>@extowner@</literal>, that string is replaced with the 817 (suitably quoted) name of the user calling <command>CREATE 818 EXTENSION</command> or <command>ALTER EXTENSION</command>. Typically 819 this feature is used by extensions that are marked trusted to assign 820 ownership of selected objects to the calling user rather than the 821 bootstrap superuser. (One should be careful about doing so, however. 822 For example, assigning ownership of a C-language function to a 823 non-superuser would create a privilege escalation path for that user.) 824 </para> 825 826 <para> 827 While the script files can contain any characters allowed by the specified 828 encoding, control files should contain only plain ASCII, because there 829 is no way for <productname>PostgreSQL</productname> to know what encoding a 830 control file is in. In practice this is only an issue if you want to 831 use non-ASCII characters in the extension's comment. Recommended 832 practice in that case is to not use the control file <varname>comment</varname> 833 parameter, but instead use <command>COMMENT ON EXTENSION</command> 834 within a script file to set the comment. 835 </para> 836 837 </sect2> 838 839 <sect2 id="extend-extensions-relocation"> 840 <title>Extension Relocatability</title> 841 842 <para> 843 Users often wish to load the objects contained in an extension into a 844 different schema than the extension's author had in mind. There are 845 three supported levels of relocatability: 846 </para> 847 848 <itemizedlist> 849 <listitem> 850 <para> 851 A fully relocatable extension can be moved into another schema 852 at any time, even after it's been loaded into a database. 853 This is done with the <command>ALTER EXTENSION SET SCHEMA</command> 854 command, which automatically renames all the member objects into 855 the new schema. Normally, this is only possible if the extension 856 contains no internal assumptions about what schema any of its 857 objects are in. Also, the extension's objects must all be in one 858 schema to begin with (ignoring objects that do not belong to any 859 schema, such as procedural languages). Mark a fully relocatable 860 extension by setting <literal>relocatable = true</literal> in its control 861 file. 862 </para> 863 </listitem> 864 865 <listitem> 866 <para> 867 An extension might be relocatable during installation but not 868 afterwards. This is typically the case if the extension's script 869 file needs to reference the target schema explicitly, for example 870 in setting <literal>search_path</literal> properties for SQL functions. 871 For such an extension, set <literal>relocatable = false</literal> in its 872 control file, and use <literal>@extschema@</literal> to refer to the target 873 schema in the script file. All occurrences of this string will be 874 replaced by the actual target schema's name before the script is 875 executed. The user can set the target schema using the 876 <literal>SCHEMA</literal> option of <command>CREATE EXTENSION</command>. 877 </para> 878 </listitem> 879 880 <listitem> 881 <para> 882 If the extension does not support relocation at all, set 883 <literal>relocatable = false</literal> in its control file, and also set 884 <literal>schema</literal> to the name of the intended target schema. This 885 will prevent use of the <literal>SCHEMA</literal> option of <command>CREATE 886 EXTENSION</command>, unless it specifies the same schema named in the control 887 file. This choice is typically necessary if the extension contains 888 internal assumptions about schema names that can't be replaced by 889 uses of <literal>@extschema@</literal>. The <literal>@extschema@</literal> 890 substitution mechanism is available in this case too, although it is 891 of limited use since the schema name is determined by the control file. 892 </para> 893 </listitem> 894 </itemizedlist> 895 896 <para> 897 In all cases, the script file will be executed with 898 <xref linkend="guc-search-path"/> initially set to point to the target 899 schema; that is, <command>CREATE EXTENSION</command> does the equivalent of 900 this: 901<programlisting> 902SET LOCAL search_path TO @extschema@, pg_temp; 903</programlisting> 904 This allows the objects created by the script file to go into the target 905 schema. The script file can change <varname>search_path</varname> if it wishes, 906 but that is generally undesirable. <varname>search_path</varname> is restored 907 to its previous setting upon completion of <command>CREATE EXTENSION</command>. 908 </para> 909 910 <para> 911 The target schema is determined by the <varname>schema</varname> parameter in 912 the control file if that is given, otherwise by the <literal>SCHEMA</literal> 913 option of <command>CREATE EXTENSION</command> if that is given, otherwise the 914 current default object creation schema (the first one in the caller's 915 <varname>search_path</varname>). When the control file <varname>schema</varname> 916 parameter is used, the target schema will be created if it doesn't 917 already exist, but in the other two cases it must already exist. 918 </para> 919 920 <para> 921 If any prerequisite extensions are listed in <varname>requires</varname> 922 in the control file, their target schemas are added to the initial 923 setting of <varname>search_path</varname>, following the new 924 extension's target schema. This allows their objects to be visible to 925 the new extension's script file. 926 </para> 927 928 <para> 929 For security, <literal>pg_temp</literal> is automatically appended to 930 the end of <varname>search_path</varname> in all cases. 931 </para> 932 933 <para> 934 Although a non-relocatable extension can contain objects spread across 935 multiple schemas, it is usually desirable to place all the objects meant 936 for external use into a single schema, which is considered the extension's 937 target schema. Such an arrangement works conveniently with the default 938 setting of <varname>search_path</varname> during creation of dependent 939 extensions. 940 </para> 941 </sect2> 942 943 <sect2 id="extend-extensions-config-tables"> 944 <title>Extension Configuration Tables</title> 945 946 <para> 947 Some extensions include configuration tables, which contain data that 948 might be added or changed by the user after installation of the 949 extension. Ordinarily, if a table is part of an extension, neither 950 the table's definition nor its content will be dumped by 951 <application>pg_dump</application>. But that behavior is undesirable for a 952 configuration table; any data changes made by the user need to be 953 included in dumps, or the extension will behave differently after a dump 954 and reload. 955 </para> 956 957 <indexterm> 958 <primary>pg_extension_config_dump</primary> 959 </indexterm> 960 961 <para> 962 To solve this problem, an extension's script file can mark a table 963 or a sequence it has created as a configuration relation, which will 964 cause <application>pg_dump</application> to include the table's or the sequence's 965 contents (not its definition) in dumps. To do that, call the function 966 <function>pg_extension_config_dump(regclass, text)</function> after creating the 967 table or the sequence, for example 968<programlisting> 969CREATE TABLE my_config (key text, value text); 970CREATE SEQUENCE my_config_seq; 971 972SELECT pg_catalog.pg_extension_config_dump('my_config', ''); 973SELECT pg_catalog.pg_extension_config_dump('my_config_seq', ''); 974</programlisting> 975 Any number of tables or sequences can be marked this way. Sequences 976 associated with <type>serial</type> or <type>bigserial</type> columns can 977 be marked as well. 978 </para> 979 980 <para> 981 When the second argument of <function>pg_extension_config_dump</function> is 982 an empty string, the entire contents of the table are dumped by 983 <application>pg_dump</application>. This is usually only correct if the table 984 is initially empty as created by the extension script. If there is 985 a mixture of initial data and user-provided data in the table, 986 the second argument of <function>pg_extension_config_dump</function> provides 987 a <literal>WHERE</literal> condition that selects the data to be dumped. 988 For example, you might do 989<programlisting> 990CREATE TABLE my_config (key text, value text, standard_entry boolean); 991 992SELECT pg_catalog.pg_extension_config_dump('my_config', 'WHERE NOT standard_entry'); 993</programlisting> 994 and then make sure that <structfield>standard_entry</structfield> is true only 995 in the rows created by the extension's script. 996 </para> 997 998 <para> 999 For sequences, the second argument of <function>pg_extension_config_dump</function> 1000 has no effect. 1001 </para> 1002 1003 <para> 1004 More complicated situations, such as initially-provided rows that might 1005 be modified by users, can be handled by creating triggers on the 1006 configuration table to ensure that modified rows are marked correctly. 1007 </para> 1008 1009 <para> 1010 You can alter the filter condition associated with a configuration table 1011 by calling <function>pg_extension_config_dump</function> again. (This would 1012 typically be useful in an extension update script.) The only way to mark 1013 a table as no longer a configuration table is to dissociate it from the 1014 extension with <command>ALTER EXTENSION ... DROP TABLE</command>. 1015 </para> 1016 1017 <para> 1018 Note that foreign key relationships between these tables will dictate the 1019 order in which the tables are dumped out by pg_dump. Specifically, pg_dump 1020 will attempt to dump the referenced-by table before the referencing table. 1021 As the foreign key relationships are set up at CREATE EXTENSION time (prior 1022 to data being loaded into the tables) circular dependencies are not 1023 supported. When circular dependencies exist, the data will still be dumped 1024 out but the dump will not be able to be restored directly and user 1025 intervention will be required. 1026 </para> 1027 1028 <para> 1029 Sequences associated with <type>serial</type> or <type>bigserial</type> columns 1030 need to be directly marked to dump their state. Marking their parent 1031 relation is not enough for this purpose. 1032 </para> 1033 </sect2> 1034 1035 <sect2> 1036 <title>Extension Updates</title> 1037 1038 <para> 1039 One advantage of the extension mechanism is that it provides convenient 1040 ways to manage updates to the SQL commands that define an extension's 1041 objects. This is done by associating a version name or number with 1042 each released version of the extension's installation script. 1043 In addition, if you want users to be able to update their databases 1044 dynamically from one version to the next, you should provide 1045 <firstterm>update scripts</firstterm> that make the necessary changes to go from 1046 one version to the next. Update scripts have names following the pattern 1047 <literal><replaceable>extension</replaceable>--<replaceable>old_version</replaceable>--<replaceable>target_version</replaceable>.sql</literal> 1048 (for example, <literal>foo--1.0--1.1.sql</literal> contains the commands to modify 1049 version <literal>1.0</literal> of extension <literal>foo</literal> into version 1050 <literal>1.1</literal>). 1051 </para> 1052 1053 <para> 1054 Given that a suitable update script is available, the command 1055 <command>ALTER EXTENSION UPDATE</command> will update an installed extension 1056 to the specified new version. The update script is run in the same 1057 environment that <command>CREATE EXTENSION</command> provides for installation 1058 scripts: in particular, <varname>search_path</varname> is set up in the same 1059 way, and any new objects created by the script are automatically added 1060 to the extension. Also, if the script chooses to drop extension member 1061 objects, they are automatically dissociated from the extension. 1062 </para> 1063 1064 <para> 1065 If an extension has secondary control files, the control parameters 1066 that are used for an update script are those associated with the script's 1067 target (new) version. 1068 </para> 1069 1070 <para> 1071 <command>ALTER EXTENSION</command> is able to execute sequences of update 1072 script files to achieve a requested update. For example, if only 1073 <literal>foo--1.0--1.1.sql</literal> and <literal>foo--1.1--2.0.sql</literal> are 1074 available, <command>ALTER EXTENSION</command> will apply them in sequence if an 1075 update to version <literal>2.0</literal> is requested when <literal>1.0</literal> is 1076 currently installed. 1077 </para> 1078 1079 <para> 1080 <productname>PostgreSQL</productname> doesn't assume anything about the properties 1081 of version names: for example, it does not know whether <literal>1.1</literal> 1082 follows <literal>1.0</literal>. It just matches up the available version names 1083 and follows the path that requires applying the fewest update scripts. 1084 (A version name can actually be any string that doesn't contain 1085 <literal>--</literal> or leading or trailing <literal>-</literal>.) 1086 </para> 1087 1088 <para> 1089 Sometimes it is useful to provide <quote>downgrade</quote> scripts, for 1090 example <literal>foo--1.1--1.0.sql</literal> to allow reverting the changes 1091 associated with version <literal>1.1</literal>. If you do that, be careful 1092 of the possibility that a downgrade script might unexpectedly 1093 get applied because it yields a shorter path. The risky case is where 1094 there is a <quote>fast path</quote> update script that jumps ahead several 1095 versions as well as a downgrade script to the fast path's start point. 1096 It might take fewer steps to apply the downgrade and then the fast 1097 path than to move ahead one version at a time. If the downgrade script 1098 drops any irreplaceable objects, this will yield undesirable results. 1099 </para> 1100 1101 <para> 1102 To check for unexpected update paths, use this command: 1103<programlisting> 1104SELECT * FROM pg_extension_update_paths('<replaceable>extension_name</replaceable>'); 1105</programlisting> 1106 This shows each pair of distinct known version names for the specified 1107 extension, together with the update path sequence that would be taken to 1108 get from the source version to the target version, or <literal>NULL</literal> if 1109 there is no available update path. The path is shown in textual form 1110 with <literal>--</literal> separators. You can use 1111 <literal>regexp_split_to_array(path,'--')</literal> if you prefer an array 1112 format. 1113 </para> 1114 </sect2> 1115 1116 <sect2> 1117 <title>Installing Extensions Using Update Scripts</title> 1118 1119 <para> 1120 An extension that has been around for awhile will probably exist in 1121 several versions, for which the author will need to write update scripts. 1122 For example, if you have released a <literal>foo</literal> extension in 1123 versions <literal>1.0</literal>, <literal>1.1</literal>, and <literal>1.2</literal>, there 1124 should be update scripts <filename>foo--1.0--1.1.sql</filename> 1125 and <filename>foo--1.1--1.2.sql</filename>. 1126 Before <productname>PostgreSQL</productname> 10, it was necessary to also create 1127 new script files <filename>foo--1.1.sql</filename> and <filename>foo--1.2.sql</filename> 1128 that directly build the newer extension versions, or else the newer 1129 versions could not be installed directly, only by 1130 installing <literal>1.0</literal> and then updating. That was tedious and 1131 duplicative, but now it's unnecessary, because <command>CREATE 1132 EXTENSION</command> can follow update chains automatically. 1133 For example, if only the script 1134 files <filename>foo--1.0.sql</filename>, <filename>foo--1.0--1.1.sql</filename>, 1135 and <filename>foo--1.1--1.2.sql</filename> are available then a request to 1136 install version <literal>1.2</literal> is honored by running those three 1137 scripts in sequence. The processing is the same as if you'd first 1138 installed <literal>1.0</literal> and then updated to <literal>1.2</literal>. 1139 (As with <command>ALTER EXTENSION UPDATE</command>, if multiple pathways are 1140 available then the shortest is preferred.) Arranging an extension's 1141 script files in this style can reduce the amount of maintenance effort 1142 needed to produce small updates. 1143 </para> 1144 1145 <para> 1146 If you use secondary (version-specific) control files with an extension 1147 maintained in this style, keep in mind that each version needs a control 1148 file even if it has no stand-alone installation script, as that control 1149 file will determine how the implicit update to that version is performed. 1150 For example, if <filename>foo--1.0.control</filename> specifies <literal>requires 1151 = 'bar'</literal> but <literal>foo</literal>'s other control files do not, the 1152 extension's dependency on <literal>bar</literal> will be dropped when updating 1153 from <literal>1.0</literal> to another version. 1154 </para> 1155 </sect2> 1156 1157 <sect2 id="extend-extensions-security"> 1158 <title>Security Considerations for Extensions</title> 1159 1160 <para> 1161 Widely-distributed extensions should assume little about the database 1162 they occupy. Therefore, it's appropriate to write functions provided 1163 by an extension in a secure style that cannot be compromised by 1164 search-path-based attacks. 1165 </para> 1166 1167 <para> 1168 An extension that has the <varname>superuser</varname> property set to 1169 true must also consider security hazards for the actions taken within 1170 its installation and update scripts. It is not terribly difficult for 1171 a malicious user to create trojan-horse objects that will compromise 1172 later execution of a carelessly-written extension script, allowing that 1173 user to acquire superuser privileges. 1174 </para> 1175 1176 <para> 1177 If an extension is marked <varname>trusted</varname>, then its 1178 installation schema can be selected by the installing user, who might 1179 intentionally use an insecure schema in hopes of gaining superuser 1180 privileges. Therefore, a trusted extension is extremely exposed from a 1181 security standpoint, and all its script commands must be carefully 1182 examined to ensure that no compromise is possible. 1183 </para> 1184 1185 <para> 1186 Advice about writing functions securely is provided in 1187 <xref linkend="extend-extensions-security-funcs"/> below, and advice 1188 about writing installation scripts securely is provided in 1189 <xref linkend="extend-extensions-security-scripts"/>. 1190 </para> 1191 1192 <sect3 id="extend-extensions-security-funcs"> 1193 <title>Security Considerations for Extension Functions</title> 1194 1195 <para> 1196 SQL-language and PL-language functions provided by extensions are at 1197 risk of search-path-based attacks when they are executed, since 1198 parsing of these functions occurs at execution time not creation time. 1199 </para> 1200 1201 <para> 1202 The <link linkend="sql-createfunction-security"><command>CREATE 1203 FUNCTION</command></link> reference page contains advice about 1204 writing <literal>SECURITY DEFINER</literal> functions safely. It's 1205 good practice to apply those techniques for any function provided by 1206 an extension, since the function might be called by a high-privilege 1207 user. 1208 </para> 1209 1210 <!-- XXX It's not enough to use qualified names, because one might write a 1211 qualified name to an object that itself uses unqualified names. Many 1212 information_schema functions have that defect, for example. However, 1213 that's a defect in the referenced object, and relatively few queries 1214 will be affected. Also, we direct applications to secure search_path 1215 when connecting to an untrusted database; if applications do that, 1216 they are immune to known attacks even if some extension refers to a 1217 defective object. Therefore, guide extension authors as though core 1218 PostgreSQL contained no such defect. --> 1219 <para> 1220 If you cannot set the <varname>search_path</varname> to contain only 1221 secure schemas, assume that each unqualified name could resolve to an 1222 object that a malicious user has defined. Beware of constructs that 1223 depend on <varname>search_path</varname> implicitly; for 1224 example, <token>IN</token> 1225 and <literal>CASE <replaceable>expression</replaceable> WHEN</literal> 1226 always select an operator using the search path. In their place, use 1227 <literal>OPERATOR(<replaceable>schema</replaceable>.=) ANY</literal> 1228 and <literal>CASE WHEN <replaceable>expression</replaceable></literal>. 1229 </para> 1230 1231 <para> 1232 A general-purpose extension usually should not assume that it's been 1233 installed into a secure schema, which means that even schema-qualified 1234 references to its own objects are not entirely risk-free. For 1235 example, if the extension has defined a 1236 function <literal>myschema.myfunc(bigint)</literal> then a call such 1237 as <literal>myschema.myfunc(42)</literal> could be captured by a 1238 hostile function <literal>myschema.myfunc(integer)</literal>. Be 1239 careful that the data types of function and operator parameters exactly 1240 match the declared argument types, using explicit casts where necessary. 1241 </para> 1242 </sect3> 1243 1244 <sect3 id="extend-extensions-security-scripts"> 1245 <title>Security Considerations for Extension Scripts</title> 1246 1247 <para> 1248 An extension installation or update script should be written to guard 1249 against search-path-based attacks occurring when the script executes. 1250 If an object reference in the script can be made to resolve to some 1251 other object than the script author intended, then a compromise might 1252 occur immediately, or later when the mis-defined extension object is 1253 used. 1254 </para> 1255 1256 <para> 1257 DDL commands such as <command>CREATE FUNCTION</command> 1258 and <command>CREATE OPERATOR CLASS</command> are generally secure, 1259 but beware of any command having a general-purpose expression as a 1260 component. For example, <command>CREATE VIEW</command> needs to be 1261 vetted, as does a <literal>DEFAULT</literal> expression 1262 in <command>CREATE FUNCTION</command>. 1263 </para> 1264 1265 <para> 1266 Sometimes an extension script might need to execute general-purpose 1267 SQL, for example to make catalog adjustments that aren't possible via 1268 DDL. Be careful to execute such commands with a 1269 secure <varname>search_path</varname>; do <emphasis>not</emphasis> 1270 trust the path provided by <command>CREATE/ALTER EXTENSION</command> 1271 to be secure. Best practice is to temporarily 1272 set <varname>search_path</varname> to <literal>'pg_catalog, 1273 pg_temp'</literal> and insert references to the extension's 1274 installation schema explicitly where needed. (This practice might 1275 also be helpful for creating views.) Examples can be found in 1276 the <filename>contrib</filename> modules in 1277 the <productname>PostgreSQL</productname> source code distribution. 1278 </para> 1279 1280 <para> 1281 Cross-extension references are extremely difficult to make fully 1282 secure, partially because of uncertainty about which schema the other 1283 extension is in. The hazards are reduced if both extensions are 1284 installed in the same schema, because then a hostile object cannot be 1285 placed ahead of the referenced extension in the installation-time 1286 <varname>search_path</varname>. However, no mechanism currently exists 1287 to require that. For now, best practice is to not mark an extension 1288 trusted if it depends on another one, unless that other one is always 1289 installed in <literal>pg_catalog</literal>. 1290 </para> 1291 1292 <para> 1293 Do <emphasis>not</emphasis> use <command>CREATE OR REPLACE 1294 FUNCTION</command>, except in an update script that must change the 1295 definition of a function that is known to be an extension member 1296 already. (Likewise for other <literal>OR REPLACE</literal> options.) 1297 Using <literal>OR REPLACE</literal> unnecessarily not only has a risk 1298 of accidentally overwriting someone else's function, but it creates a 1299 security hazard since the overwritten function would still be owned by 1300 its original owner, who could modify it. 1301 </para> 1302 </sect3> 1303 </sect2> 1304 1305 <sect2 id="extend-extensions-example"> 1306 <title>Extension Example</title> 1307 1308 <para> 1309 Here is a complete example of an <acronym>SQL</acronym>-only 1310 extension, a two-element composite type that can store any type of value 1311 in its slots, which are named <quote>k</quote> and <quote>v</quote>. Non-text 1312 values are automatically coerced to text for storage. 1313 </para> 1314 1315 <para> 1316 The script file <filename>pair--1.0.sql</filename> looks like this: 1317 1318<programlisting><![CDATA[ 1319-- complain if script is sourced in psql, rather than via CREATE EXTENSION 1320\echo Use "CREATE EXTENSION pair" to load this file. \quit 1321 1322CREATE TYPE pair AS ( k text, v text ); 1323 1324CREATE FUNCTION pair(text, text) 1325RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1, $2)::@extschema@.pair;'; 1326 1327CREATE OPERATOR ~> (LEFTARG = text, RIGHTARG = text, FUNCTION = pair); 1328 1329-- "SET search_path" is easy to get right, but qualified names perform better. 1330CREATE FUNCTION lower(pair) 1331RETURNS pair LANGUAGE SQL 1332AS 'SELECT ROW(lower($1.k), lower($1.v))::@extschema@.pair;' 1333SET search_path = pg_temp; 1334 1335CREATE FUNCTION pair_concat(pair, pair) 1336RETURNS pair LANGUAGE SQL 1337AS 'SELECT ROW($1.k OPERATOR(pg_catalog.||) $2.k, 1338 $1.v OPERATOR(pg_catalog.||) $2.v)::@extschema@.pair;'; 1339]]> 1340</programlisting> 1341 </para> 1342 1343 <para> 1344 The control file <filename>pair.control</filename> looks like this: 1345 1346<programlisting> 1347# pair extension 1348comment = 'A key/value pair data type' 1349default_version = '1.0' 1350# cannot be relocatable because of use of @extschema@ 1351relocatable = false 1352</programlisting> 1353 </para> 1354 1355 <para> 1356 While you hardly need a makefile to install these two files into the 1357 correct directory, you could use a <filename>Makefile</filename> containing this: 1358 1359<programlisting> 1360EXTENSION = pair 1361DATA = pair--1.0.sql 1362 1363PG_CONFIG = pg_config 1364PGXS := $(shell $(PG_CONFIG) --pgxs) 1365include $(PGXS) 1366</programlisting> 1367 1368 This makefile relies on <acronym>PGXS</acronym>, which is described 1369 in <xref linkend="extend-pgxs"/>. The command <literal>make install</literal> 1370 will install the control and script files into the correct 1371 directory as reported by <application>pg_config</application>. 1372 </para> 1373 1374 <para> 1375 Once the files are installed, use the 1376 <xref linkend="sql-createextension"/> command to load the objects into 1377 any particular database. 1378 </para> 1379 </sect2> 1380 </sect1> 1381 1382 <sect1 id="extend-pgxs"> 1383 <title>Extension Building Infrastructure</title> 1384 1385 <indexterm zone="extend-pgxs"> 1386 <primary>pgxs</primary> 1387 </indexterm> 1388 1389 <para> 1390 If you are thinking about distributing your 1391 <productname>PostgreSQL</productname> extension modules, setting up a 1392 portable build system for them can be fairly difficult. Therefore 1393 the <productname>PostgreSQL</productname> installation provides a build 1394 infrastructure for extensions, called <acronym>PGXS</acronym>, so 1395 that simple extension modules can be built simply against an 1396 already installed server. <acronym>PGXS</acronym> is mainly intended 1397 for extensions that include C code, although it can be used for 1398 pure-SQL extensions too. Note that <acronym>PGXS</acronym> is not 1399 intended to be a universal build system framework that can be used 1400 to build any software interfacing to <productname>PostgreSQL</productname>; 1401 it simply automates common build rules for simple server extension 1402 modules. For more complicated packages, you might need to write your 1403 own build system. 1404 </para> 1405 1406 <para> 1407 To use the <acronym>PGXS</acronym> infrastructure for your extension, 1408 you must write a simple makefile. 1409 In the makefile, you need to set some variables 1410 and include the global <acronym>PGXS</acronym> makefile. 1411 Here is an example that builds an extension module named 1412 <literal>isbn_issn</literal>, consisting of a shared library containing 1413 some C code, an extension control file, a SQL script, an include file 1414 (only needed if other modules might need to access the extension functions 1415 without going via SQL), and a documentation text file: 1416<programlisting> 1417MODULES = isbn_issn 1418EXTENSION = isbn_issn 1419DATA = isbn_issn--1.0.sql 1420DOCS = README.isbn_issn 1421HEADERS_isbn_issn = isbn_issn.h 1422 1423PG_CONFIG = pg_config 1424PGXS := $(shell $(PG_CONFIG) --pgxs) 1425include $(PGXS) 1426</programlisting> 1427 The last three lines should always be the same. Earlier in the 1428 file, you assign variables or add custom 1429 <application>make</application> rules. 1430 </para> 1431 1432 <para> 1433 Set one of these three variables to specify what is built: 1434 1435 <variablelist> 1436 <varlistentry> 1437 <term><varname>MODULES</varname></term> 1438 <listitem> 1439 <para> 1440 list of shared-library objects to be built from source files with same 1441 stem (do not include library suffixes in this list) 1442 </para> 1443 </listitem> 1444 </varlistentry> 1445 1446 <varlistentry> 1447 <term><varname>MODULE_big</varname></term> 1448 <listitem> 1449 <para> 1450 a shared library to build from multiple source files 1451 (list object files in <varname>OBJS</varname>) 1452 </para> 1453 </listitem> 1454 </varlistentry> 1455 1456 <varlistentry> 1457 <term><varname>PROGRAM</varname></term> 1458 <listitem> 1459 <para> 1460 an executable program to build 1461 (list object files in <varname>OBJS</varname>) 1462 </para> 1463 </listitem> 1464 </varlistentry> 1465 </variablelist> 1466 1467 The following variables can also be set: 1468 1469 <variablelist> 1470 <varlistentry> 1471 <term><varname>EXTENSION</varname></term> 1472 <listitem> 1473 <para> 1474 extension name(s); for each name you must provide an 1475 <literal><replaceable>extension</replaceable>.control</literal> file, 1476 which will be installed into 1477 <literal><replaceable>prefix</replaceable>/share/extension</literal> 1478 </para> 1479 </listitem> 1480 </varlistentry> 1481 1482 <varlistentry> 1483 <term><varname>MODULEDIR</varname></term> 1484 <listitem> 1485 <para> 1486 subdirectory of <literal><replaceable>prefix</replaceable>/share</literal> 1487 into which DATA and DOCS files should be installed 1488 (if not set, default is <literal>extension</literal> if 1489 <varname>EXTENSION</varname> is set, 1490 or <literal>contrib</literal> if not) 1491 </para> 1492 </listitem> 1493 </varlistentry> 1494 1495 <varlistentry> 1496 <term><varname>DATA</varname></term> 1497 <listitem> 1498 <para> 1499 random files to install into <literal><replaceable>prefix</replaceable>/share/$MODULEDIR</literal> 1500 </para> 1501 </listitem> 1502 </varlistentry> 1503 1504 <varlistentry> 1505 <term><varname>DATA_built</varname></term> 1506 <listitem> 1507 <para> 1508 random files to install into 1509 <literal><replaceable>prefix</replaceable>/share/$MODULEDIR</literal>, 1510 which need to be built first 1511 </para> 1512 </listitem> 1513 </varlistentry> 1514 1515 <varlistentry> 1516 <term><varname>DATA_TSEARCH</varname></term> 1517 <listitem> 1518 <para> 1519 random files to install under 1520 <literal><replaceable>prefix</replaceable>/share/tsearch_data</literal> 1521 </para> 1522 </listitem> 1523 </varlistentry> 1524 1525 <varlistentry> 1526 <term><varname>DOCS</varname></term> 1527 <listitem> 1528 <para> 1529 random files to install under 1530 <literal><replaceable>prefix</replaceable>/doc/$MODULEDIR</literal> 1531 </para> 1532 </listitem> 1533 </varlistentry> 1534 1535 <varlistentry> 1536 <term><varname>HEADERS</varname></term> 1537 <term><varname>HEADERS_built</varname></term> 1538 <listitem> 1539 <para> 1540 Files to (optionally build and) install under 1541 <literal><replaceable>prefix</replaceable>/include/server/$MODULEDIR/$MODULE_big</literal>. 1542 </para> 1543 <para> 1544 Unlike <literal>DATA_built</literal>, files in <literal>HEADERS_built</literal> 1545 are not removed by the <literal>clean</literal> target; if you want them removed, 1546 also add them to <literal>EXTRA_CLEAN</literal> or add your own rules to do it. 1547 </para> 1548 </listitem> 1549 </varlistentry> 1550 1551 <varlistentry> 1552 <term><varname>HEADERS_$MODULE</varname></term> 1553 <term><varname>HEADERS_built_$MODULE</varname></term> 1554 <listitem> 1555 <para> 1556 Files to install (after building if specified) under 1557 <literal><replaceable>prefix</replaceable>/include/server/$MODULEDIR/$MODULE</literal>, 1558 where <literal>$MODULE</literal> must be a module name used 1559 in <literal>MODULES</literal> or <literal>MODULE_big</literal>. 1560 </para> 1561 <para> 1562 Unlike <literal>DATA_built</literal>, files in <literal>HEADERS_built_$MODULE</literal> 1563 are not removed by the <literal>clean</literal> target; if you want them removed, 1564 also add them to <literal>EXTRA_CLEAN</literal> or add your own rules to do it. 1565 </para> 1566 <para> 1567 It is legal to use both variables for the same module, or any 1568 combination, unless you have two module names in the 1569 <literal>MODULES</literal> list that differ only by the presence of a 1570 prefix <literal>built_</literal>, which would cause ambiguity. In 1571 that (hopefully unlikely) case, you should use only the 1572 <literal>HEADERS_built_$MODULE</literal> variables. 1573 </para> 1574 </listitem> 1575 </varlistentry> 1576 1577 <varlistentry> 1578 <term><varname>SCRIPTS</varname></term> 1579 <listitem> 1580 <para> 1581 script files (not binaries) to install into 1582 <literal><replaceable>prefix</replaceable>/bin</literal> 1583 </para> 1584 </listitem> 1585 </varlistentry> 1586 1587 <varlistentry> 1588 <term><varname>SCRIPTS_built</varname></term> 1589 <listitem> 1590 <para> 1591 script files (not binaries) to install into 1592 <literal><replaceable>prefix</replaceable>/bin</literal>, 1593 which need to be built first 1594 </para> 1595 </listitem> 1596 </varlistentry> 1597 1598 <varlistentry> 1599 <term><varname>REGRESS</varname></term> 1600 <listitem> 1601 <para> 1602 list of regression test cases (without suffix), see below 1603 </para> 1604 </listitem> 1605 </varlistentry> 1606 1607 <varlistentry> 1608 <term><varname>REGRESS_OPTS</varname></term> 1609 <listitem> 1610 <para> 1611 additional switches to pass to <application>pg_regress</application> 1612 </para> 1613 </listitem> 1614 </varlistentry> 1615 1616 <varlistentry> 1617 <term><varname>ISOLATION</varname></term> 1618 <listitem> 1619 <para> 1620 list of isolation test cases, see below for more details 1621 </para> 1622 </listitem> 1623 </varlistentry> 1624 1625 <varlistentry> 1626 <term><varname>ISOLATION_OPTS</varname></term> 1627 <listitem> 1628 <para> 1629 additional switches to pass to 1630 <application>pg_isolation_regress</application> 1631 </para> 1632 </listitem> 1633 </varlistentry> 1634 1635 <varlistentry> 1636 <term><varname>TAP_TESTS</varname></term> 1637 <listitem> 1638 <para> 1639 switch defining if TAP tests need to be run, see below 1640 </para> 1641 </listitem> 1642 </varlistentry> 1643 1644 <varlistentry> 1645 <term><varname>NO_INSTALLCHECK</varname></term> 1646 <listitem> 1647 <para> 1648 don't define an <literal>installcheck</literal> target, useful e.g., if tests require special configuration, or don't use <application>pg_regress</application> 1649 </para> 1650 </listitem> 1651 </varlistentry> 1652 1653 <varlistentry> 1654 <term><varname>EXTRA_CLEAN</varname></term> 1655 <listitem> 1656 <para> 1657 extra files to remove in <literal>make clean</literal> 1658 </para> 1659 </listitem> 1660 </varlistentry> 1661 1662 <varlistentry> 1663 <term><varname>PG_CPPFLAGS</varname></term> 1664 <listitem> 1665 <para> 1666 will be prepended to <varname>CPPFLAGS</varname> 1667 </para> 1668 </listitem> 1669 </varlistentry> 1670 1671 <varlistentry> 1672 <term><varname>PG_CFLAGS</varname></term> 1673 <listitem> 1674 <para> 1675 will be appended to <varname>CFLAGS</varname> 1676 </para> 1677 </listitem> 1678 </varlistentry> 1679 1680 <varlistentry> 1681 <term><varname>PG_CXXFLAGS</varname></term> 1682 <listitem> 1683 <para> 1684 will be appended to <varname>CXXFLAGS</varname> 1685 </para> 1686 </listitem> 1687 </varlistentry> 1688 1689 <varlistentry> 1690 <term><varname>PG_LDFLAGS</varname></term> 1691 <listitem> 1692 <para> 1693 will be prepended to <varname>LDFLAGS</varname> 1694 </para> 1695 </listitem> 1696 </varlistentry> 1697 1698 <varlistentry> 1699 <term><varname>PG_LIBS</varname></term> 1700 <listitem> 1701 <para> 1702 will be added to <varname>PROGRAM</varname> link line 1703 </para> 1704 </listitem> 1705 </varlistentry> 1706 1707 <varlistentry> 1708 <term><varname>SHLIB_LINK</varname></term> 1709 <listitem> 1710 <para> 1711 will be added to <varname>MODULE_big</varname> link line 1712 </para> 1713 </listitem> 1714 </varlistentry> 1715 1716 <varlistentry> 1717 <term><varname>PG_CONFIG</varname></term> 1718 <listitem> 1719 <para> 1720 path to <application>pg_config</application> program for the 1721 <productname>PostgreSQL</productname> installation to build against 1722 (typically just <literal>pg_config</literal> to use the first one in your 1723 <varname>PATH</varname>) 1724 </para> 1725 </listitem> 1726 </varlistentry> 1727 </variablelist> 1728 </para> 1729 1730 <para> 1731 Put this makefile as <literal>Makefile</literal> in the directory 1732 which holds your extension. Then you can do 1733 <literal>make</literal> to compile, and then <literal>make 1734 install</literal> to install your module. By default, the extension is 1735 compiled and installed for the 1736 <productname>PostgreSQL</productname> installation that 1737 corresponds to the first <command>pg_config</command> program 1738 found in your <varname>PATH</varname>. You can use a different installation by 1739 setting <varname>PG_CONFIG</varname> to point to its 1740 <command>pg_config</command> program, either within the makefile 1741 or on the <literal>make</literal> command line. 1742 </para> 1743 1744 <para> 1745 You can also run <literal>make</literal> in a directory outside the source 1746 tree of your extension, if you want to keep the build directory separate. 1747 This procedure is also called a 1748 <indexterm><primary>VPATH</primary></indexterm><firstterm>VPATH</firstterm> 1749 build. Here's how: 1750<programlisting> 1751mkdir build_dir 1752cd build_dir 1753make -f /path/to/extension/source/tree/Makefile 1754make -f /path/to/extension/source/tree/Makefile install 1755</programlisting> 1756 </para> 1757 1758 <para> 1759 Alternatively, you can set up a directory for a VPATH build in a similar 1760 way to how it is done for the core code. One way to do this is using the 1761 core script <filename>config/prep_buildtree</filename>. Once this has been done 1762 you can build by setting the <literal>make</literal> variable 1763 <varname>VPATH</varname> like this: 1764<programlisting> 1765make VPATH=/path/to/extension/source/tree 1766make VPATH=/path/to/extension/source/tree install 1767</programlisting> 1768 This procedure can work with a greater variety of directory layouts. 1769 </para> 1770 1771 <para> 1772 The scripts listed in the <varname>REGRESS</varname> variable are used for 1773 regression testing of your module, which can be invoked by <literal>make 1774 installcheck</literal> after doing <literal>make install</literal>. For this to 1775 work you must have a running <productname>PostgreSQL</productname> server. 1776 The script files listed in <varname>REGRESS</varname> must appear in a 1777 subdirectory named <literal>sql/</literal> in your extension's directory. 1778 These files must have extension <literal>.sql</literal>, which must not be 1779 included in the <varname>REGRESS</varname> list in the makefile. For each 1780 test there should also be a file containing the expected output in a 1781 subdirectory named <literal>expected/</literal>, with the same stem and 1782 extension <literal>.out</literal>. <literal>make installcheck</literal> 1783 executes each test script with <application>psql</application>, and compares the 1784 resulting output to the matching expected file. Any differences will be 1785 written to the file <literal>regression.diffs</literal> in <command>diff 1786 -c</command> format. Note that trying to run a test that is missing its 1787 expected file will be reported as <quote>trouble</quote>, so make sure you 1788 have all expected files. 1789 </para> 1790 1791 <para> 1792 The scripts listed in the <varname>ISOLATION</varname> variable are used 1793 for tests stressing behavior of concurrent session with your module, which 1794 can be invoked by <literal>make installcheck</literal> after doing 1795 <literal>make install</literal>. For this to work you must have a 1796 running <productname>PostgreSQL</productname> server. The script files 1797 listed in <varname>ISOLATION</varname> must appear in a subdirectory 1798 named <literal>specs/</literal> in your extension's directory. These files 1799 must have extension <literal>.spec</literal>, which must not be included 1800 in the <varname>ISOLATION</varname> list in the makefile. For each test 1801 there should also be a file containing the expected output in a 1802 subdirectory named <literal>expected/</literal>, with the same stem and 1803 extension <literal>.out</literal>. <literal>make installcheck</literal> 1804 executes each test script, and compares the resulting output to the 1805 matching expected file. Any differences will be written to the file 1806 <literal>output_iso/regression.diffs</literal> in 1807 <command>diff -c</command> format. Note that trying to run a test that is 1808 missing its expected file will be reported as <quote>trouble</quote>, so 1809 make sure you have all expected files. 1810 </para> 1811 1812 <para> 1813 <literal>TAP_TESTS</literal> enables the use of TAP tests. Data from each 1814 run is present in a subdirectory named <literal>tmp_check/</literal>. 1815 See also <xref linkend="regress-tap"/> for more details. 1816 </para> 1817 1818 <tip> 1819 <para> 1820 The easiest way to create the expected files is to create empty files, 1821 then do a test run (which will of course report differences). Inspect 1822 the actual result files found in the <literal>results/</literal> 1823 directory (for tests in <literal>REGRESS</literal>), or 1824 <literal>output_iso/results/</literal> directory (for tests in 1825 <literal>ISOLATION</literal>), then copy them to 1826 <literal>expected/</literal> if they match what you expect from the test. 1827 </para> 1828 1829 </tip> 1830 </sect1> 1831 1832 </chapter> 1833