1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
|
<!-- manual page source format generated by PolyglotMan v3.0.4, -->
<!-- available via anonymous ftp from ftp.cs.berkeley.edu:/ucb/people/phelps/tcltk/rman.tar.Z -->
<HTML>
<HEAD>
<TITLE>"CREATE(LANGUAGE") manual page</TITLE>
</HEAD>
<BODY>
<A HREF="sql.html">SQL Reference Contents</A>
<H2><A NAME="sect0" HREF="#toc0">NAME </A></H2>
create language - define a new language for functions
<H2><A NAME="sect1" HREF="#toc1">SYNOPSIS </A></H2>
<B>create
</B> [<B>trusted </B>] <B>procedural language </B> 'lanname' <BR>
<tt> </tt><tt> </tt><B>handler </B> call_handler <BR>
<tt> </tt><tt> </tt><B>lancompiler
</B> 'comment' <BR>
<H2><A NAME="sect2" HREF="#toc2">DESCRIPTION </A></H2>
With this command, a Postgres user can register
a new language with Postgres. Subsequently, functions and trigger procedures
can be defined in this new language. The user must have the Postgres superuser
privilege to register a new language. <P>
The lanname is the name of the new
procedural language. It is converted to lower case before the new entry
in the pg_language system catalog is inserted. Note that this case translation
is also done on create <A HREF="function.l.html">function(l)</A>
and drop <A HREF="language.l.html">language(l)</A>
. Thus, the language
name is case insensitive. A procedural language cannot override one of
the builtin languages of Postgres. <P>
The argument for <B>handler </B> is the name
of a previously registered function that will be called to execute the
PL procedures. <P>
The <B>lancompiler </B> argument is the string that will be inserted
in the lancompiler attribute of the new pg_language entry. Up to now, Postgres
doesn't use this attribute in any way. <P>
The <B>trusted </B> keyword specifies,
that the call handler for the language is safe - i.e. it offers an unprivileged
user no functionality to get around access restrictions. If this keyword
is omitted when registering the language, only users with the Postgres
superuser privilege can use this language to create new functions (like
the 'C' language).
<H2><A NAME="sect3" HREF="#toc3">WRITING PL HANDLERS </A></H2>
The call handler for a procedural
language must be written in a compiler language such as 'C' and registered
with Postgres as a function taking no arguments and returning <I>opaque</I> type.
This prevents the call handler from beeing called directly as a function
from queries. But there are arguments on the actual call when a PL function
or trigger procedure in the language offered by the handler is to be executed.
<P>
When called from the trigger manager, the only argument is the object
ID from the procedures pg_proc entry. All other information from the trigger
manager is found in the global CurrentTriggerData pointer. <P>
When called
from the function manager, the arguments are the object ID of the procedures
pg_proc entry, the number of arguments given to the PL function, the arguments
in a FmgrValues structure and a pointer to a boolean where the function
tells the caller if the return value is the SQL NULL value. <P>
It's up to the
call handler to fetch the pg_proc entry and to analyze the argument and
return types of the called procedure. the <I>as</I> clause from the create <A HREF="function.l.html">function(l)</A>
of the procedure will be found in the prosrc attribute of the pg_proc
entry. This may be the source text in the procedural language itself (like
for PL/Tcl), a pathname to a file or anything else that tells the call
handler what to do in detail.
<H2><A NAME="sect4" HREF="#toc4">EXAMPLE </A></H2>
Following is a template for a PL
handler written in 'C': <P>
#include "executor/spi.h" <BR>
#include "commands/trigger.h"
<BR>
#include "utils/elog.h" <BR>
#include "fmgr.h"<tt> </tt><tt> </tt><tt> </tt><tt> </tt>/* for FmgrValues struct */ <BR>
#include "access/heapam.h" <BR>
#include "utils/syscache.h" <BR>
#include "catalog/pg_proc.h"
<BR>
#include "catalog/pg_type.h" <BR>
<P>
Datum <BR>
plsample_call_handler( <BR>
<tt> </tt><tt> </tt>Oid<tt> </tt><tt> </tt><tt> </tt><tt> </tt>prooid,
<BR>
<tt> </tt><tt> </tt>int<tt> </tt><tt> </tt><tt> </tt><tt> </tt>pronargs, <BR>
<tt> </tt><tt> </tt>FmgrValues<tt> </tt><tt> </tt>*proargs, <BR>
<tt> </tt><tt> </tt>bool<tt> </tt><tt> </tt><tt> </tt><tt> </tt>*isNull) <BR>
{ <BR>
<tt> </tt><tt> </tt>Datum<tt> </tt><tt> </tt><tt> </tt><tt> </tt>retval; <BR>
<tt> </tt><tt> </tt>TriggerData<tt> </tt><tt> </tt>*trigdata;
<BR>
<P>
<tt> </tt><tt> </tt>if (CurrentTriggerData == NULL) { <BR>
<tt> </tt><tt> </tt><tt> </tt><tt> </tt>/* <BR>
<tt> </tt><tt> </tt><tt> </tt><tt> </tt> * Called as a function <BR>
<tt> </tt><tt> </tt><tt> </tt><tt> </tt> */
<BR>
<P>
<tt> </tt><tt> </tt><tt> </tt><tt> </tt>retval = ... <BR>
<tt> </tt><tt> </tt>} else { <BR>
<tt> </tt><tt> </tt><tt> </tt><tt> </tt>/* <BR>
<tt> </tt><tt> </tt><tt> </tt><tt> </tt> * Called as a trigger procedure <BR>
<tt> </tt><tt> </tt><tt> </tt><tt> </tt> */ <BR>
<tt> </tt><tt> </tt><tt> </tt><tt> </tt>trigdata
= CurrentTriggerData; <BR>
<tt> </tt><tt> </tt><tt> </tt><tt> </tt>CurrentTriggerData = NULL; <BR>
<P>
<tt> </tt><tt> </tt><tt> </tt><tt> </tt>retval = ... <BR>
<tt> </tt><tt> </tt>} <BR>
<P>
<tt> </tt><tt> </tt>*isNull
= false; <BR>
<tt> </tt><tt> </tt>return retval; <BR>
} <BR>
<P>
Only a few thousand lines of code have to
be added instead of the dots to complete the PL call handler. See create
<A HREF="function.l.html">function(l)</A>
how to compile it into a loadable module. The following commands
then register the sample procedural language. <P>
create function plsample_call_handler
() returns opaque <BR>
<tt> </tt><tt> </tt>as '/usr/lib/postgresql/lib/plsample.so' <BR>
<tt> </tt><tt> </tt>language 'C'; <BR>
<P>
create
procedural language 'plsample' <BR>
<tt> </tt><tt> </tt>handler plsample_call_handler <BR>
<tt> </tt><tt> </tt>lancompiler
'PL/Sample'; <BR>
<P>
<H2><A NAME="sect5" HREF="#toc5">SEE ALSO </A></H2>
<P>
create <A HREF="function.l.html">function(l)</A>
, drop <A HREF="language.l.html">language(l)</A>
.
<H2><A NAME="sect6" HREF="#toc6">RESTRICTIONS
</A></H2>
Since the call handler for a procedural language must be registered with
Postgres in the 'C' language, it inherits all the restrictions of 'C' functions.
<H2><A NAME="sect7" HREF="#toc7">BUGS </A></H2>
Currently, the definitions for a procedural language once created
cannot be changed. <P>
<HR><P>
<A NAME="toc"><B>Table of Contents</B></A><P>
<UL>
<LI><A NAME="toc0" HREF="#sect0">NAME</A></LI>
<LI><A NAME="toc1" HREF="#sect1">SYNOPSIS</A></LI>
<LI><A NAME="toc2" HREF="#sect2">DESCRIPTION</A></LI>
<LI><A NAME="toc3" HREF="#sect3">WRITING PL HANDLERS</A></LI>
<LI><A NAME="toc4" HREF="#sect4">EXAMPLE</A></LI>
<LI><A NAME="toc5" HREF="#sect5">SEE ALSO</A></LI>
<LI><A NAME="toc6" HREF="#sect6">RESTRICTIONS</A></LI>
<LI><A NAME="toc7" HREF="#sect7">BUGS</A></LI>
</UL>
</BODY></HTML>
|
