> --II//uussrr//llooccaall//ppoossttggrreess9955//ssrrcc//bbaacckkeenndd//oobbjj (where <
> is the name of the port, e.g.,
aallpphhaa or ssppaarrcc))..
3333
(2) When allocating memory, use the POSTGRES rou-
tines ppaalllloocc and ppffrreeee instead of the corre-
sponding C library routines mmaalllloocc and ffrreeee.
The memory allocated by ppaalllloocc will be freed
automatically at the end of each transaction,
preventing memory leaks.
(3) Always zero the bytes of your structures using
mmeemmsseett or bbzzeerroo. Several routines (such as the
hash access method, hash join and the sort algo-
rithm) compute functions of the raw bits con-
tained in your structure. Even if you initial-
ize all fields of your structure, there may be
several bytes of alignment padding (holes in the
structure) that may contain garbage values.
(4) Most of the internal POSTGRES types are declared
in ppoossttggrreess..hh, so it's usually a good idea to
include that file as well.
(5) Compiling and loading your object code so that
it can be dynamically loaded into POSTGRES
always requires special flags. See Appendix A
for a detailed explanation of how to do it for
your particular operating system.
3344
________________________________________________________________________________________________________________________
88.. EEXXTTEENNDDIINNGG SSQQLL:: TTYYPPEESS
________________________________________________________________________________________________________________________
As previously mentioned, there are two kinds of types
in POSTGRES: _b_a_s_e types (defined in a programming lan-
guage) and _c_o_m_p_o_s_i_t_e types (instances).
Examples in this section up to interfacing indices can
be found in ccoommpplleexx..ssqqlland ccoommpplleexx..cc. Composite exam-
ples are in ffuunnccss..ssqqll.
88..11.. UUsseerr--DDeeffiinneedd TTyyppeess
________________________________________________________________________________________________________________________
88..11..11.. FFuunnccttiioonnss NNeeeeddeedd ffoorr aa UUsseerr--DDeeffiinneedd TTyyppee
A user-defined type must always have _i_n_p_u_t and _o_u_t_p_u_t
functions. These functions determine how the type
appears in strings (for input by the user and output to
the user) and how the type is organized in memory. The
input function takes a null-delimited character string
as its input and returns the internal (in memory) rep-
resentation of the type. The output function takes the
internal representation of the type and returns a null-
delimited character string.
Suppose we want to define a ccoommpplleexx type which repre-
sents complex numbers. Naturally, we choose to repre-
sent a complex in memory as the following C structure:
ttyyppeeddeeff ssttrruucctt CCoommpplleexx {{
ddoouubbllee xx;;
ddoouubbllee yy;;
}} CCoommpplleexx;;
and a string of the form ((xx,,yy)) as the external string
representation.
These functions are usually not hard to write, espe-
cially the output function. However, there are a num-
ber of points to remember.
(1) When defining your external (string) representa-
tion, remember that you must eventually write a
complete and robust parser for that representa-
tion as your input function!
CCoommpplleexx **
ccoommpplleexx__iinn((cchhaarr **ssttrr))
{{
ddoouubbllee xx,, yy;;
CCoommpplleexx **rreessuulltt;;
3355
iiff ((ssssccaannff((ssttrr,, "" (( %%llff ,, %%llff ))"",, &&xx,, &&yy)) !!== 22)) {{
eelloogg((WWAARRNN,, ""ccoommpplleexx__iinn:: eerrrroorr iinn ppaarrssiinngg
rreettuurrnn NNUULLLL;;
}}
rreessuulltt == ((CCoommpplleexx **))ppaalllloocc((ssiizzeeooff((CCoommpplleexx))));;
rreessuulltt-->>xx == xx;;
rreessuulltt-->>yy == yy;;
rreettuurrnn ((rreessuulltt));;
}}
The output function can simply be:
cchhaarr **
ccoommpplleexx__oouutt((CCoommpplleexx **ccoommpplleexx))
{{
cchhaarr **rreessuulltt;;
iiff ((ccoommpplleexx ==== NNUULLLL))
rreettuurrnn((NNUULLLL));;
rreessuulltt == ((cchhaarr **)) ppaalllloocc((6600));;
sspprriinnttff((rreessuulltt,, ""((%%gg,,%%gg))"",, ccoommpplleexx-->>xx,, ccoommpplleexx-->>yy));;
rreettuurrnn((rreessuulltt));;
}}
(2) You should try to make the input and output
functions inverses of each other. If you do
not, you will have severe problems when you need
to dump your data into a file and then read it
back in (say, into someone else's database on
another computer). This is a particularly com-
mon problem when floating-point numbers are
involved.
To define the ccoommpplleexx type, we need to create the two
user-defined functions ccoommpplleexx__iinn and ccoommpplleexx__oouutt
before creating the type:
CCRREEAATTEE FFUUNNCCTTIIOONN ccoommpplleexx__iinn((ooppaaqquuee))
RREETTUURRNNSS ccoommpplleexx
AASS ''//uussrr//llooccaall//ppoossttggrreess9955//ttuuttoorriiaall//oobbjj//ccoommpplleexx..ssoo''
LLAANNGGUUAAGGEE ''cc'';;
CCRREEAATTEE FFUUNNCCTTIIOONN ccoommpplleexx__oouutt((ooppaaqquuee))
RREETTUURRNNSS ooppaaqquuee
AASS ''//uussrr//llooccaall//ppoossttggrreess9955//ttuuttoorriiaall//oobbjj//ccoommpplleexx..ssoo''
LLAANNGGUUAAGGEE ''cc'';;
CCRREEAATTEE TTYYPPEE ccoommpplleexx ((
iinntteerrnnaalllleennggtthh == 1166,,
iinnppuutt == ccoommpplleexx__iinn,,
oouuttppuutt == ccoommpplleexx__oouutt
));;
3366
As discussed earlier, POSTGRES fully supports arrays of
base types. Additionally, POSTGRES supports arrays of
user-defined types as well. When you define a type,
POSTGRES automatically provides support for arrays of
that type. For historical reasons, the array type has
the same name as the user-defined type with the under-
score character __ prepended.
Composite types do not need any function defined on
them, since the system already understands what they
look like inside.
88..11..22.. LLaarrggee OObbjjeeccttss
The types discussed to this point are all "small"
objects -- that is, they are smaller than 8KB7 in size.
If you require a larger type for something like a docu-
ment retrieval system or for storing bitmaps, you will
need to use the POSTGRES _l_a_r_g_e _o_b_j_e_c_t interface.
____________________
7 8 * 1024 == 8192 bytes. In fact, the type must be con-
siderably smaller than 8192 bytes, since the POSTGRES tuple
and page overhead must also fit into this 8KB limitation.
The actual value that fits depends on the machine architec-
ture.
3377
________________________________________________________________________________________________________________________
99.. EEXXTTEENNDDIINNGG SSQQLL:: OOPPEERRAATTOORRSS
________________________________________________________________________________________________________________________
POSTGRES supports left unary, right unary and binary
operators. Operators can be _o_v_e_r_l_o_a_d_e_d, or re-used
with different numbers and types of arguments. If
there is an ambiguous situation and the system cannot
determine the correct operator to use, it will return
an error and you may have to typecast the left and/or
right operands to help it understand which operator you
meant to use.
To create an operator for adding two complex numbers
can be done as follows. First we need to create a
function to add the new types. Then, we can create the
operator with the function.
CCRREEAATTEE FFUUNNCCTTIIOONN ccoommpplleexx__aadddd((ccoommpplleexx,, ccoommpplleexx))
RREETTUURRNNSS ccoommpplleexx
AASS ''$$PPWWDD//oobbjj//ccoommpplleexx..ssoo''
LLAANNGGUUAAGGEE ''cc'';;
CCRREEAATTEE OOPPEERRAATTOORR ++ ((
lleeffttaarrgg == ccoommpplleexx,,
rriigghhttaarrgg == ccoommpplleexx,,
pprroocceedduurree == ccoommpplleexx__aadddd,,
ccoommmmuuttaattoorr == ++
));;
We've shown how to create a binary operator here. To
create unary operators, just omit one of lleeffttaarrgg (for
left unary) or rriigghhttaarrgg (for right unary).
If we give the system enough type information, it can
automatically figure out which operators to use.
SSEELLEECCTT ((aa ++ bb)) AASS cc FFRROOMM tteesstt__ccoommpplleexx;;
+----------------+
|cc |
+----------------+
|((55..22,,66..0055)) |
+----------------+
|((113333..4422,,114444..9955)) |
+----------------+
3388
________________________________________________________________________________________________________________________
1100.. EEXXTTEENNDDIINNGG SSQQLL:: AAGGGGRREEGGAATTEESS
________________________________________________________________________________________________________________________
Aggregates in POSTGRES are expressed in terms of _s_t_a_t_e
_t_r_a_n_s_i_t_i_o_n _f_u_n_c_t_i_o_n_s. That is, an aggregate can be
defined in terms of _s_t_a_t_e that is modified whenever an
instance is processed. Some state functions look at a
particular value in the instance when computing the new
state (_s_f_u_n_c_1 in the ccrreeaattee aaggggrreeggaattee syntax) while
others only keep track of their own internal state
(_s_f_u_n_c_2).
If we define an aggregate that uses only ssffuunncc11, we
define an aggregate that computes a running function of
the attribute values from each instance. "Sum" is an
example of this kind of aggregate. "Sum" starts at
zero and always adds the current instance's value to
its running total. We will use the iinntt44ppll that is
built into POSTGRES to perform this addition.
CCRREEAATTEE AAGGGGRREEGGAATTEE ccoommpplleexx__ssuumm ((
ssffuunncc11 == ccoommpplleexx__aadddd,,
bbaasseettyyppee == ccoommpplleexx,,
ssttyyppee11 == ccoommpplleexx,,
iinniittccoonndd11 == ''((00,,00))''
));;
SSEELLEECCTT ccoommpplleexx__ssuumm((aa)) FFRROOMM tteesstt__ccoommpplleexx;;
+------------+
|ccoommpplleexx__ssuumm |
+------------+
|((3344,,5533..99)) |
+------------+
If we define only ssffuunncc22, we are specifying an aggre-
gate that computes a running function that is indepen-
dent of the attribute values from each instance.
"Count" is the most common example of this kind of
aggregate. "Count" starts at zero and adds one to its
running total for each instance, ignoring the instance
value. Here, we use the built-in iinntt44iinncc routine to do
the work for us. This routine increments (adds one to)
its argument.
CCRREEAATTEE AAGGGGRREEGGAATTEE mmyy__ccoouunntt ((ssffuunncc22 == iinntt44iinncc,, ---- aadddd oonnee
bbaasseettyyppee == iinntt44,, ssttyyppee22 == iinntt44,,
3399
iinniittccoonndd22 == ''00''))
SSEELLEECCTT mmyy__ccoouunntt((**)) aass eemmpp__ccoouunntt ffrroomm EEMMPP;;
+----------+
|eemmpp__ccoouunntt |
+----------+
|55 |
+----------+
"Average" is an example of an aggregate that requires
both a function to compute the running sum and a func-
tion to compute the running count. When all of the
instances have been processed, the final answer for the
aggregate is the running sum divided by the running
count. We use the iinntt44ppll and iinntt44iinncc routines we used
before as well as the POSTGRES integer division rou-
tine, iinntt44ddiivv, to compute the division of the sum by
the count.
CCRREEAATTEE AAGGGGRREEGGAATTEE mmyy__aavveerraaggee ((ssffuunncc11 == iinntt44ppll,, ---- ssuumm
bbaasseettyyppee == iinntt44,,
ssttyyppee11 == iinntt44,,
ssffuunncc22 == iinntt44iinncc,, ---- ccoouunntt
ssttyyppee22 == iinntt44,,
ffiinnaallffuunncc == iinntt44ddiivv,, ---- ddiivviissiioonn
iinniittccoonndd11 == ''00'',,
iinniittccoonndd22 == ''00''))
SSEELLEECCTT mmyy__aavveerraaggee((ssaallaarryy)) aass eemmpp__aavveerraaggee FFRROOMM EEMMPP;;
+------------+
|eemmpp__aavveerraaggee |
+------------+
|11664400 |
+------------+
4400
________________________________________________________________________________________________________________________
1111.. IINNTTEERRFFAACCIINNGG EEXXTTEENNSSIIOONNSS TTOO IINNDDIICCEESS
________________________________________________________________________________________________________________________
The procedures described thus far let you define a new
type, new functions and new operators. However, we
cannot yet define a secondary index (such as a B-tree,
R-tree or hash access method) over a new type or its
operators.
Look back at Figure 3. The right half shows the cata-
logs that we must modify in order to tell POSTGRES how
to use a user-defined type and/or user-defined opera-
tors with an index (i.e., ppgg__aamm, ppgg__aammoopp, ppgg__aammpprroocc and
ppgg__ooppccllaassss). Unfortunately, there is no simple command
to do this. We will demonstrate how to modify these
catalogs through a running example: a new operator
class for the B-tree access method that sorts integers
in ascending absolute value order.
The ppgg__aamm class contains one instance for every user-
defined access method. Support for the heap access
method is built into POSTGRES, but every other access
method is described here. The schema is
+-------------+---------------------------------------------------------------+
|aammnnaammee | name of the access method |
+-------------+---------------------------------------------------------------+
|aammoowwnneerr | object id of the owner's instance in pg_user |
+-------------+---------------------------------------------------------------+
|aammkkiinndd | not used at present, but set to 'o' as a place holder |
+-------------+---------------------------------------------------------------+
|aammssttrraatteeggiieess | number of strategies for this access method (see below) |
+-------------+---------------------------------------------------------------+
|aammssuuppppoorrtt | number of support routines for this access method (see below) |
+-------------+---------------------------------------------------------------+
|aammggeettttuuppllee | procedure identifiers for interface routines to the access |
|aammiinnsseerrtt | method. For example, rreeggpprroocc ids for opening, closing, and |
|...... | getting instances from the access method appear here. |
+-------------+---------------------------------------------------------------+
The object ID of the instance in ppgg__aamm is used as a
foreign key in lots of other classes. You don't need
to add a new instance to this class; all you're inter-
ested in is the object ID of the access method instance
you want to extend:
SSEELLEECCTT ooiidd FFRROOMM ppgg__aamm WWHHEERREE aammnnaammee == ''bbttrreeee''
4411
+----+
|ooiidd |
+----+
|440033 |
+----+
The aammssttrraatteeggiieess attribute exists to standardize com-
parisons across data types. For example, B-trees
impose a strict ordering on keys, lesser to greater.
Since POSTGRES allows the user to define operators,
POSTGRES cannot look at the nnaammee of an operator (eg, >>
or <<) and tell what kind of comparison it is. In fact,
some access methods don't impose any ordering at all.
For example, R-trees express a rectangle-containment
relationship, whereas a hashed data structure expresses
only bitwise similarity based on the value of a hash
function. POSTGRES needs some consistent way of taking
a qualification in your query, looking at the operator
and then deciding if a usable index exists. This
implies that POSTGRES needs to know, for example, that
the <<== and >> operators partition a B-tree. POSTGRES
uses strategies to express these relationships between
operators and the way they can be used to scan indices.
Defining a new set of strategies is beyond the scope of
this discussion, but we'll explain how B-tree strate-
gies work because you'll need to know that to add a new
operator class. In the ppgg__aamm class, the aammssttrraatteeggiieess
attribute is the number of strategies defined for this
access method. For B-trees, this number is 5. These
strategies correspond to
+----------------------+---+
|less than | 1 |
+----------------------+---+
|less than or equal | 2 |
+----------------------+---+
|equal | 3 |
+----------------------+---+
|greater than or equal | 4 |
+----------------------+---+
|greater than | 5 |
+----------------------+---+
The idea is that you'll need to add procedures corre-
sponding to the comparisons above to the ppgg__aammoopp rela-
tion (see below). The access method code can use these
strategy numbers, regardless of data type, to figure
out how to partition the B-tree, compute selectivity,
and so on. Don't worry about the details of adding
procedures yet; just understand that there must be a
set of these procedures for iinntt22, iinntt44, ooiidd, and every
other data type on which a B-tree can operate.
4422
Sometimes, strategies aren't enough information for the
system to figure out how to use an index. Some access
methods require other support routines in order to
work. For example, the B-tree access method must be
able to compare two keys and determine whether one is
greater than, equal to, or less than the other. Simi-
larly, the R-tree access method must be able to compute
intersections, unions, and sizes of rectangles. These
operations do not correspond to user qualifications in
SQL queries; they are administrative routines used by
the access methods, internally.
In order to manage diverse support routines consis-
tently across all POSTGRES access methods, ppgg__aamm
includes an attribute called aammssuuppppoorrtt. This attribute
records the number of support routines used by an
access method. For B-trees, this number is one -- the
routine to take two keys and return -1, 0, or +1,
depending on whether the first key is less than, equal
to, or greater than the second.8
The aammssttrraatteeggiieess entry in ppgg__aamm is just the _n_u_m_b_e_r of
strategies defined for the access method in question.
The procedures for less than, less equal, and so on
don't appear in ppgg__aamm. Similarly, aammssuuppppoorrtt is just
the number of support routines required by the access
method. The actual routines are listed elsewhere.
The next class of interest is ppgg__ooppccllaassss.. This class
exists only to associate a name with an ooiidd. In
ppgg__aammoopp, every B-tree operator class has a set of pro-
cedures, one through five, above. Some existing
opclasses are iinntt22__ooppss, iinntt44__ooppss, and ooiidd__ooppss. You
need to add an instance with your opclass name (for
example, ccoommpplleexx__aabbss__ooppss) to ppgg__ooppccllaassss. The ooiidd of
this instance is a foreign key in other classes.
IINNSSEERRTT IINNTTOO ppgg__ooppccllaassss ((ooppccnnaammee)) VVAALLUUEESS ((''ccoommpplleexx__aabbss__ooppss''));;
SSEELLEECCTT ooiidd,, ooppccnnaammee
FFRROOMM ppgg__ooppccllaassss
WWHHEERREE ooppccnnaammee == ''ccoommpplleexx__aabbss__ooppss'';;
+------+--------------+
|ooiidd | ooppccnnaammee |
+------+--------------+
|1177331144 | iinntt44__aabbss__ooppss |
+------+--------------+
Note that the ooiidd for your ppgg__ooppccllaassss instance wwiillll bbee
ddiiffffeerreenntt! You should substitute your value for 17314
____________________
8 Strictly speaking, this routine can return a negative
number (< 0), 0, or a non-zero positive number (> 0).
4433
wherever it appears in this discussion.
So now we have an access method and an operator class.
We still need a set of operators; the procedure for
defining operators was discussed earlier in this man-
ual. For the ccoommpplleexx__aabbss__ooppss operator class on B-
trees, the operators we require are:
absolute value less-than
absolute value less-than-or-equal
absolute value equal
absolute value greater-than-or-equal
absolute value greater-than
Suppose the code that implements the functions defined
is stored in the file
//uussrr//llooccaall//ppoossttggrreess9955//ssrrcc//ttuuttoorriiaall//ccoommpplleexx..cc
Part of the code look like this: (note that we will
only show the equality operator for the rest of the
examples. The other four operators are very similar.
Refer to ccoommpplleexx..cc or ccoommpplleexx..ssqqll for the details.)
##ddeeffiinnee MMaagg((cc)) ((((cc))-->>xx**((cc))-->>xx ++ ((cc))-->>yy**((cc))-->>yy))
bbooooll
ccoommpplleexx__aabbss__eeqq((CCoommpplleexx **aa,, CCoommpplleexx **bb))
{{
ddoouubbllee aammaagg == MMaagg((aa)),, bbmmaagg == MMaagg((bb));;
rreettuurrnn ((aammaagg====bbmmaagg));;
}}
There are a couple of important things that are happen-
ing below.
First, note that operators for less-than, less-than-or-
equal, equal, greater-than-or-equal, and greater-than
for iinntt44 are being defined. All of these operators are
already defined for iinntt44 under the names <<, <<==, ==, >>==,
and >>. The new operators behave differently, of
course. In order to guarantee that POSTGRES uses these
new operators rather than the old ones, they need to be
named differently from the old ones. This is a key
point: you can overload operators in POSTGRES, but only
if the operator isn't already defined for the argument
types. That is, if you have << defined for (iinntt44,
iinntt44), you can't define it again. POSTGRES ddooeess nnoott
cchheecckk this when you define your operator, so be care-
ful. To avoid this problem, odd names will be used for
the operators. If you get this wrong, the access meth-
ods are likely to crash when you try to do scans.
The other important point is that all the operator
functions return _B_o_o_l_e_a_n values. The access methods
rely on this fact. (On the other hand, the support
4444
function returns whatever the particular access method
expects -- in this case, a signed integer.)
The final routine in the file is the "support routine"
mentioned when we discussed the aammssuuppppoorrtt attribute of
the ppgg__aamm class. We will use this later on. For now,
ignore it.
CCRREEAATTEE FFUUNNCCTTIIOONN ccoommpplleexx__aabbss__eeqq((ccoommpplleexx,, ccoommpplleexx))
RREETTUURRNNSS bbooooll
AASS ''//uussrr//llooccaall//ppoossttggrreess9955//ttuuttoorriiaall//oobbjj//ccoommpplleexx..ssoo''
LLAANNGGUUAAGGEE ''cc'';;
Now define the operators that use them. As noted, the
operator names must be unique among all operators that
take two iinntt44 operands. In order to see if the opera-
tor names listed below are taken, we can do a query on
ppgg__ooppeerraattoorr:
//**
** tthhiiss qquueerryy uusseess tthhee rreegguullaarr eexxpprreessssiioonn ooppeerraattoorr ((~~))
** ttoo ffiinndd tthhrreeee--cchhaarraacctteerr ooppeerraattoorr nnaammeess tthhaatt eenndd iinn
** tthhee cchhaarraacctteerr &&
**//
SSEELLEECCTT **
FFRROOMM ppgg__ooppeerraattoorr
WWHHEERREE oopprrnnaammee ~~ ''^^....&&$$''::::tteexxtt;;
to see if your name is taken for the types you want.
The important things here are the procedure (which are
the C functions defined above) and the restriction and
join selectivity functions. You should just use the
ones used below--note that there are different such
functions for the less-than, equal, and greater-than
cases. These _m_u_s_t be supplied, or the access method
will crash when it tries to use the operator. You
should copy the names for rreessttrriicctt and jjooiinn, but use
the procedure names you defined in the last step.
CCRREEAATTEE OOPPEERRAATTOORR == ((
lleeffttaarrgg == ccoommpplleexx,, rriigghhttaarrgg == ccoommpplleexx,, pprroocceedduurree == ccoommpplleexx__aabbss__eeqq,,
rreessttrriicctt == eeqqsseell,, jjooiinn == eeqqjjooiinnsseell
))
Notice that five operators corresponding to less, less
equal, equal, greater, and greater equal are defined.
We're just about finished. the last thing we need to do
is to update the ppgg__aammoopp relation. To do this, we need
the following attributes:
4455
+------------+----------------------+
|aammooppiidd | the ooiidd of the ppgg__aamm |
| | instance for B-tree |
| | (== 403, see above) |
+------------+----------------------+
|aammooppccllaaiidd | the ooiidd of the |
| | ppgg__ooppccllaassss instance |
| | for iinntt44__aabbss__ooppss (== |
| | whatever you got in- |
| | stead of 17314, see |
| | above) |
+------------+----------------------+
|aammooppoopprr | the ooiidds of the op- |
| | erators for the op- |
| | class (which we'll |
| | get in just a |
| | minute) |
+------------+----------------------+
|aammooppsseelleecctt,, | cost functions. |
|aammooppnnppaaggeess | |
+------------+----------------------+
The cost functions are used by the query optimizer to
decide whether or not to use a given index in a scan.
Fortunately, these already exist. The two functions
we'll use are bbttrreeeesseell,, which estimates the selectivity
of the B-tree, and bbttrreeeennppaaggee,, which estimates the num-
ber of pages a search will touch in the tree.
So we need the ooiidds of the operators we just defined.
We'll look up the names of all the operators that take
two iinntt44s, and pick ours out:
SSEELLEECCTT oo..ooiidd AASS ooppooiidd,, oo..oopprrnnaammee
IINNTTOO TTAABBLLEE ccoommpplleexx__ooppss__ttmmpp
FFRROOMM ppgg__ooppeerraattoorr oo,, ppgg__ttyyppee tt
WWHHEERREE oo..oopprrlleefftt == tt..ooiidd aanndd oo..oopprrrriigghhtt == tt..ooiidd
aanndd tt..ttyyppnnaammee == ''ccoommpplleexx'';;
which returns:
4466
+------+---------+
|ooiidd | oopprrnnaammee |
+------+---------+
|1177332211 | << |
+------+---------+
|1177332222 | <<== |
+------+---------+
|1177332233 | == |
+------+---------+
|1177332244 | >>== |
+------+---------+
|1177332255 | >> |
+------+---------+
(Again, some of your ooiidd numbers will almost certainly
be different.) The operators we are interested in are
those with ooiidds 17321 through 17325. The values you
get will probably be different, and you should substi-
tute them for the values below. We can look at the
operator names and pick out the ones we just added.
Now we're ready to update ppgg__aammoopp with our new operator
class. The most important thing in this entire discus-
sion is that the operators are ordered, from less equal
through greater equal, in ppgg__aammoopp. We add the
instances we need:
IINNSSEERRTT IINNTTOO ppgg__aammoopp ((aammooppiidd,, aammooppccllaaiidd,, aammooppoopprr,, aammooppssttrraatteeggyy,,
aammooppsseelleecctt,, aammooppnnppaaggeess))
SSEELLEECCTT aamm..ooiidd,, ooppccll..ooiidd,, cc..ooppooiidd,, 33,,
''bbttrreeeesseell''::::rreeggpprroocc,, ''bbttrreeeennppaaggee''::::rreeggpprroocc
FFRROOMM ppgg__aamm aamm,, ppgg__ooppccllaassss ooppccll,, ccoommpplleexx__ooppss__ttmmpp cc
WWHHEERREE aammnnaammee == ''bbttrreeee'' aanndd ooppccnnaammee == ''ccoommpplleexx__aabbss__ooppss''
aanndd cc..oopprrnnaammee == ''=='';;
Note the order: "less than" is 1, "less than or equal"
is 2, "equal" is 3, "greater than or equal" is 4, and
"greater than" is 5.
The last step (finally!) is registration of the "sup-
port routine" previously described in our discussion of
ppgg__aamm. The ooiidd of this support routine is stored in
the ppgg__aammpprroocc class, keyed by the access method ooiidd and
the operator class ooiidd. First, we need to register the
function in POSTGRES (recall that we put the C code
that implements this routine in the bottom of the file
in which we implemented the operator routines):
CCRREEAATTEE FFUUNNCCTTIIOONN iinntt44__aabbss__ccmmpp((iinntt44,, iinntt44))
RREETTUURRNNSS iinntt44
AASS ''//uussrr//llooccaall//ppoossttggrreess9955//ttuuttoorriiaall//oobbjj//ccoommpplleexx..ssoo''
LLAANNGGUUAAGGEE ''cc'';;
4477
SSEELLEECCTT ooiidd,, pprroonnaammee FFRROOMM ppgg__pprroocc WWHHEERREE pprrnnaammee == ''iinntt44__aabbss__ccmmpp'';;
+------+--------------+
|ooiidd | pprroonnaammee |
+------+--------------+
|1177332288 | iinntt44__aabbss__ccmmpp |
+------+--------------+
(Again, your ooiidd number will probably be different and
you should substitute the value you see for the value
below.) Recalling that the B-tree instance's ooiidd is
403 and that of iinntt44__aabbss__ooppss is 17314, we can add the
new instance as follows:
IINNSSEERRTT IINNTTOO ppgg__aammpprroocc ((aammiidd,, aammooppccllaaiidd,, aammpprroocc,, aammpprrooccnnuumm))
VVAALLUUEESS ((''440033''::::ooiidd,, ---- bbttrreeee ooiidd
''1177331144''::::ooiidd,, ---- ppgg__ooppccllaassss ttuuppllee
''1177332288''::::ooiidd,, ---- nneeww ppgg__pprroocc ooiidd
''11''::::iinntt22));;
4488
________________________________________________________________________________________________________________________
1122.. LLIIBBPPQQ
________________________________________________________________________________________________________________________
LIBPQ is the application programming interface to POST-
GRES. LIBPQ is a set of library routines which allows
client programs to pass queries to the POSTGRES backend
server and to receive the results of these queries.
This version of the documentation describes the C
interface library. Three short programs are included
at the end of this section to show how to write pro-
grams that use LIBPQ.
There are several examples of LIBPQ applications in the
following directories:
....//ssrrcc//tteesstt//rreeggrreessss
....//ssrrcc//tteesstt//eexxaammpplleess
....//ssrrcc//bbiinn//ppssqqll
Frontend programs which use LIBPQ must include the
header file lliibbppqq--ffee..hh and must link with the lliibbppqq
library.
1122..11.. CCoonnttrrooll aanndd IInniittiiaalliizzaattiioonn
The following environment variables can be used to set
up default environment values to avoid hard-coding
database names into an application program:
+o PPGGHHOOSSTT sets the default server name.
+o PPGGOOPPTTIIOONNSS sets additional runtime options for the
POSTGRES backend.
+o PPGGPPOORRTT sets the default port for communicating with
the POSTGRES backend.
+o PPGGTTTTYY sets the file or tty on which debugging mes-
sages from the backend server are displayed.
+o PPGGDDAATTAABBAASSEE sets the default POSTGRES database name.
+o PPGGRREEAALLMM sets the _K_e_r_b_e_r_o_s realm to use with POST-
GRES, if it is different from the local realm. If
PPGGRREEAALLMM is set, POSTGRES applications will attempt
authentication with servers for this realm and use
separate ticket files to avoid conflicts with local
ticket files. This environment variable is only
used if _K_e_r_b_e_r_o_s authentication is enabled.
1122..22.. DDaattaabbaassee CCoonnnneeccttiioonn FFuunnccttiioonnss
The following routines deal with making a connection to
a backend from a C program.
PPQQsseettddbb
4499
Makes a new connection to a backend.
PPGGccoonnnn **PPQQsseettddbb((cchhaarr **ppgghhoosstt,,
cchhaarr **ppggppoorrtt,,
cchhaarr **ppggooppttiioonnss,,
cchhaarr **ppggttttyy,,
cchhaarr **ddbbNNaammee));;
If any argument is NULL, then the corresponding
environment variable is checked. If the environ-
ment variable is also not set, then hardwired
defaults are used.
_P_Q_s_e_t_d_b always returns a valid PGconn pointer.
The _P_Q_s_t_a_t_u_s (see below) command should be called
to ensure that a connection was properly made
before queries are sent via the connection. LIBPQ
programmers should be careful to maintain the
PGconn abstraction. Use the accessor functions
below to get at the contents of PGconn. Avoid
directly referencing the fields of the PGconn
structure as they are subject to change in the
future.
PPQQddbb returns the database name of the connection.
cchhaarr **PPQQddbb((PPGGccoonnnn **ccoonnnn))
PPQQhhoosstt returns the host name of the connection.
cchhaarr **PPQQhhoosstt((PPGGccoonnnn **ccoonnnn))
PPQQooppttiioonnss returns the pgoptions used in the con-
nection.
cchhaarr **PPQQooppttiioonnss((PPGGccoonnnn **ccoonnnn))
PPQQppoorrtt returns the pgport of the connection.
cchhaarr **PPQQppoorrtt((PPGGccoonnnn **ccoonnnn))
PPQQttttyy returns the pgtty of the connection.
cchhaarr **PPQQttttyy((PPGGccoonnnn **ccoonnnn))
PPQQssttaattuuss Returns the status of the connection. The
status can be CONNECTION_OK or CONNECTION_BAD.
CCoonnnnSSttaattuussTTyyppee **PPQQssttaattuuss((PPGGccoonnnn **ccoonnnn))
PPQQeerrrroorrMMeessssaaggee returns the error message associ-
ated with the connection
cchhaarr **PPQQeerrrroorrMMeessssaaggee((PPGGccoonnnn** ccoonnnn));;
5500
PPQQffiinniisshh
Close the connection to the backend. Also frees
memory used by the PGconn structure. The PGconn
pointer should not be used after PQfinish has been
called.
vvooiidd PPQQffiinniisshh((PPGGccoonnnn **ccoonnnn))
PPQQrreesseett
Reset the communication port with the backend.
This function will close the IPC socket connection
to the backend and attempt to reestablish a new
connection to the same backend.
vvooiidd PPQQrreesseett((PPGGccoonnnn **ccoonnnn))
PPQQttrraaccee
Enables tracing of messages passed between the
frontend and the backend. The messages are echoed
to the debug_port file stream.
vvooiidd PPQQttrraaccee((PPGGccoonnnn **ccoonnnn,,
FFIILLEE** ddeebbuugg__ppoorrtt));;
PPQQuunnttrraaccee
Disables tracing of messages passed between the
frontend and the backend.
vvooiidd PPQQuunnttrraaccee((PPGGccoonnnn **ccoonnnn));;
1122..33.. QQuueerryy EExxeeccuuttiioonn FFuunnccttiioonnss
PPQQeexxeecc
Submit a query to POSTGRES. Returns a PGresult
pointer if the query was successful or a NULL oth-
erwise. If a NULL is returned, _P_Q_e_r_r_o_r_M_e_s_s_a_g_e can
be used to get more information about the error.
PPGGrreessuulltt **PPQQeexxeecc((PPGGccoonnnn **ccoonnnn,,
cchhaarr **qquueerryy));;
The PGresult structure encapsulates the query
result returned by the backend. LIBPQ programmers
should be careful to maintain the PGresult
abstraction. Use the accessor functions described
below to retrieve the results of the query. Avoid
directly referencing the fields of the PGresult
structure as they are subject to change in the
future.
PPQQrreessuullttSSttaattuuss
Returns the result status of the query. _P_Q_r_e_s_u_l_t_-
_S_t_a_t_u_s can return one of the following values:
PPGGRREESS__EEMMPPTTYY__QQUUEERRYY,,
5511
PPGGRREESS__CCOOMMMMAANNDD__OOKK,, //** tthhee qquueerryy wwaass aa ccoommmmaanndd **//
PPGGRREESS__TTUUPPLLEESS__OOKK,, //** tthhee qquueerryy ssuucccceessssffuullllyy rreettuurrnneedd ttuupplleess **//
PPGGRREESS__CCOOPPYY__OOUUTT,,
PPGGRREESS__CCOOPPYY__IINN,,
PPGGRREESS__BBAADD__RREESSPPOONNSSEE,, //** aann uunneexxppeecctteedd rreessppoonnssee wwaass rreecceeiivveedd **//
PPGGRREESS__NNOONNFFAATTAALL__EERRRROORR,,
PPGGRREESS__FFAATTAALL__EERRRROORR
If the result status is PGRES_TUPLES_OK, then the
following routines can be used to retrieve the
tuples returned by the query.
PPQQnnttuupplleess returns the number of tuples (instances)
in the query result.
iinntt PPQQnnttuupplleess((PPGGrreessuulltt **rreess));;
PPQQnnffiieellddss returns the number of fields
(attributes) in the query result.
iinntt PPQQnnffiieellddss((PPGGrreessuulltt **rreess));;
PPQQffnnaammee returns the field (attribute) name associ-
ated with the given field index. Field indices
start at 0.
cchhaarr **PPQQffnnaammee((PPGGrreessuulltt **rreess,,
iinntt ffiieelldd__iinnddeexx));;
PPQQffnnuummbbeerr returns the field (attribute) index
associated with the given field name.
iinntt PPQQffnnuummbbeerr((PPGGrreessuulltt **rreess,,
cchhaarr** ffiieelldd__nnaammee));;
PPQQffttyyppee returns the field type associated with the
given field index. The integer returned is an
internal coding of the type. Field indices start
at 0.
OOiidd PPQQffttyyppee((PPGGrreessuulltt **rreess,,
iinntt ffiieelldd__nnuumm));;
PPQQffssiizzee returns the size in bytes of the field
associated with the given field index. If the size
returned is -1, the field is a variable length
field. Field indices start at 0.
iinntt22 PPQQffssiizzee((PPGGrreessuulltt **rreess,,
iinntt ffiieelldd__iinnddeexx));;
PPQQggeettvvaalluuee returns the field (attribute) value.
For most queries, the value returned by _P_Q_g_e_t_v_a_l_u_e
is a null-terminated ASCII string representation
5522
of the attribute value. If the query was a result
of a BBIINNAARRYY cursor, then the value returned by
_P_Q_g_e_t_v_a_l_u_e is the binary representation of the
type in the internal format of the backend server.
It is the programmer's responsibility to cast and
convert the data to the correct C type. The value
returned by _P_Q_g_e_t_v_a_l_u_e points to storage that is
part of the PGresult structure. One must explic-
itly copy the value into other storage if it is to
be used past the lifetime of the PGresult struc-
ture itself.
cchhaarr** PPQQggeettvvaalluuee((PPGGrreessuulltt **rreess,,
iinntt ttuupp__nnuumm,,
iinntt ffiieelldd__nnuumm));;
PPQQggeettlleennggtthh returns the length of a field
(attribute) in bytes. If the field is a _s_t_r_u_c_t
_v_a_r_l_e_n_a, the length returned here does nnoott include
the size field of the varlena, i.e., it is 4 bytes
less.
iinntt PPQQggeettlleennggtthh((PPGGrreessuulltt **rreess,,
iinntt ttuupp__nnuumm,,
iinntt ffiieelldd__nnuumm));;
PPQQccmmddSSttaattuuss
Returns the command status associated with the
last query command.
cchhaarr **PPQQccmmddSSttaattuuss((PPGGrreessuulltt **rreess));;
PPQQooiiddSSttaattuuss
Returns a string with the object id of the tuple
inserted if the last query is an INSERT command.
Otherwise, returns an empty string.
cchhaarr** PPQQooiiddSSttaattuuss((PPGGrreessuulltt **rreess));;
PPQQpprriinnttTTuupplleess
Prints out all the tuples and, optionally, the
attribute names to the specified output stream.
The programs ppssqqll and mmoonniittoorr both use _P_Q_p_r_i_n_t_-
_T_u_p_l_e_s for output.
vvooiidd PPQQpprriinnttTTuupplleess((
PPGGrreessuulltt** rreess,,
FFIILLEE** ffoouutt,, //** oouuttppuutt ssttrreeaamm **//
iinntt pprriinnttAAttttNNaammee,,//** pprriinntt aattttrriibbuuttee nnaammeess oorr nnoott**//
iinntt tteerrsseeOOuuttppuutt,, //** ddeelliimmiitteerr bbaarrss oorr nnoott??**//
iinntt wwiiddtthh //** wwiiddtthh ooff ccoolluummnn,, vvaarriiaabbllee wwiiddtthh iiff 00**//
));;
5533
PPQQcclleeaarr
Frees the storage associated with the PGresult.
Every query result should be properly freed when
it is no longer used. Failure to do this will
result in memory leaks in the frontend applica-
tion.
vvooiidd PPQQcclleeaarr((PPQQrreessuulltt **rreess));;
1122..44.. FFaasstt PPaatthh
POSTGRES provides a ffaasstt ppaatthh interface to send func-
tion calls to the backend. This is a trapdoor into
system internals and can be a potential security hole.
Most users will not need this feature.
PPGGrreessuulltt** PPQQffnn((PPGGccoonnnn** ccoonnnn,,
iinntt ffnniidd,,
iinntt **rreessuulltt__bbuuff,,
iinntt **rreessuulltt__lleenn,,
iinntt rreessuulltt__iiss__iinntt,,
PPQQAArrggBBlloocckk **aarrggss,,
iinntt nnaarrggss));;
The _f_n_i_d argument is the object identifier of the func-
tion to be executed. _r_e_s_u_l_t___b_u_f is the buffer in which
to load the return value. The caller must have allo-
cated sufficient space to store the return value. The
result length will be returned in the storage pointed
to by _r_e_s_u_l_t___l_e_n_. If the result is to be an integer
value, than _r_e_s_u_l_t___i_s___i_n_t should be set to 1; otherwise
it should be set to 0. _a_r_g_s and _n_a_r_g_s specify the
arguments to the function.
ttyyppeeddeeff ssttrruucctt {{
iinntt lleenn;;
iinntt iissiinntt;;
uunniioonn {{
iinntt **ppttrr;;
iinntt iinntteeggeerr;;
}} uu;;
}} PPQQAArrggBBlloocckk;;
_P_Q_f_n always returns a valid PGresult*. The resultSta-
tus should be checked before the result is used. The
caller is responsible for freeing the PGresult with
_P_Q_c_l_e_a_r when it is not longer needed.
1122..55.. AAssyynncchhrroonnoouuss NNoottiiffiiccaattiioonn
POSTGRES supports asynchronous notification via the
_L_I_S_T_E_N and _N_O_T_I_F_Y commands. A backend registers its
interest in a particular relation with the LISTEN com-
mand. All backends listening on a particular relation
will be notified asynchronously when a NOTIFY of that
relation name is executed by another backend. No
5544
additional information is passed from the notifier to
the listener. Thus, typically, any actual data that
needs to be communicated is transferred through the
relation.
LIBPQ applications are notified whenever a connected
backend has received an asynchronous notification.
However, the communication from the backend to the
frontend is not asynchronous. Notification comes
piggy-backed on other query results. Thus, an applica-
tion must submit queries, even empty ones, in order to
receive notice of backend notification. In effect, the
LIBPQ application must poll the backend to see if there
is any pending notification information. After the
execution of a query, a frontend may call _P_Q_N_o_t_i_f_i_e_s to
see if any notification data is available from the
backend.
PPQQNNoottiiffiieess
returns the notification from a list of unhandled
notifications from the backend. Returns NULL if
there are no pending notifications from the back-
end. _P_Q_N_o_t_i_f_i_e_s behaves like the popping of a
stack. Once a notification is returned from _P_Q_n_o_-
_t_i_f_i_e_s_, it is considered handled and will be
removed from the list of notifications.
PPGGnnoottiiffyy** PPQQNNoottiiffiieess((PPGGccoonnnn **ccoonnnn));;
The second sample program gives an example of the use
of asynchronous notification.
1122..66.. FFuunnccttiioonnss AAssssoocciiaatteedd wwiitthh tthhee CCOOPPYY CCoommmmaanndd
The _c_o_p_y command in POSTGRES has options to read from
or write to the network connection used by LIBPQ.
Therefore, functions are necessary to access this net-
work connection directly so applications may take full
advantage of this capability.
PPQQggeettlliinnee
Reads a newline-terminated line of characters
(transmitted by the backend server) into a buffer
_s_t_r_i_n_g of size _l_e_n_g_t_h. Like _f_g_e_t_s(3), this rou-
tine copies up to _l_e_n_g_t_h-1 characters into _s_t_r_i_n_g.
It is like _g_e_t_s(3), however, in that it converts
the terminating newline into a null character.
_P_Q_g_e_t_l_i_n_e returns EOF at EOF, 0 if the entire line
has been read, and 1 if the buffer is full but the
terminating newline has not yet been read.
Notice that the application must check to see if a
new line consists of the single character ".",
which indicates that the backend server has fin-
ished sending the results of the _c_o_p_y command.
Therefore, if the application ever expects to
receive lines that are more than _l_e_n_g_t_h-1 charac-
ters long, the application must be sure to check
the return value of _P_Q_g_e_t_l_i_n_e very carefully.
5555
The code in
....//ssrrcc//bbiinn//ppssqqll//ppssqqll..cc
contains routines that correctly handle the copy
protocol.
iinntt PPQQggeettlliinnee((PPGGccoonnnn **ccoonnnn,,
cchhaarr **ssttrriinngg,,
iinntt lleennggtthh))
PPQQppuuttlliinnee
Sends a null-terminated _s_t_r_i_n_g to the backend
server.
The application must explicitly send the single
character "." to indicate to the backend that it
has finished sending its data.
vvooiidd PPQQppuuttlliinnee((PPGGccoonnnn **ccoonnnn,,
cchhaarr **ssttrriinngg));;
PPQQeennddccooppyy
Syncs with the backend. This function waits until
the backend has finished the copy. It should
either be issued when the last string has been
sent to the backend using _P_Q_p_u_t_l_i_n_e or when the
last string has been received from the backend
using _P_G_g_e_t_l_i_n_e. It must be issued or the backend
may get "out of sync" with the frontend. Upon
return from this function, the backend is ready to
receive the next query.
The return value is 0 on successful completion,
nonzero otherwise.
iinntt PPQQeennddccooppyy((PPGGccoonnnn **ccoonnnn));;
As an example:
PPQQeexxeecc((ccoonnnn,, ""ccrreeaattee ttaabbllee ffoooo ((aa iinntt44,, bb cchhaarr1166,, dd ffllooaatt88))""));;
PPQQeexxeecc((ccoonnnn,, ""ccooppyy ffoooo ffrroomm ssttddiinn""));;
PPQQppuuttlliinnee((ccoonnnn,, ""33<>
##iinncclluuddee ""lliibbppqq--ffee..hh""
vvooiidd
eexxiitt__nniicceellyy((PPGGccoonnnn** ccoonnnn))
{{
PPQQffiinniisshh((ccoonnnn));;
eexxiitt((11));;
}}
mmaaiinn(())
{{
cchhaarr **ppgghhoosstt,, **ppggppoorrtt,, **ppggooppttiioonnss,, **ppggttttyy;;
cchhaarr** ddbbNNaammee;;
iinntt nnFFiieellddss;;
iinntt ii,,jj;;
//** FFIILLEE **ddeebbuugg;; **//
PPGGccoonnnn** ccoonnnn;;
PPGGrreessuulltt** rreess;;
//** bbeeggiinn,, bbyy sseettttiinngg tthhee ppaarraammeetteerrss ffoorr aa bbaacckkeenndd ccoonnnneeccttiioonn
iiff tthhee ppaarraammeetteerrss aarree nnuullll,, tthheenn tthhee ssyysstteemm wwiillll ttrryy ttoo uussee
rreeaassoonnaabbllee ddeeffaauullttss bbyy llooookkiinngg uupp eennvviirroonnmmeenntt vvaarriiaabblleess
oorr,, ffaaiilliinngg tthhaatt,, uussiinngg hhaarrddwwiirreedd ccoonnssttaannttss **//
ppgghhoosstt == NNUULLLL;; //** hhoosstt nnaammee ooff tthhee bbaacckkeenndd sseerrvveerr **//
ppggppoorrtt == NNUULLLL;; //** ppoorrtt ooff tthhee bbaacckkeenndd sseerrvveerr **//
ppggooppttiioonnss == NNUULLLL;; //** ssppeecciiaall ooppttiioonnss ttoo ssttaarrtt uupp tthhee bbaacckkeenndd sseerrvveerr **//
ppggttttyy == NNUULLLL;; //** ddeebbuuggggiinngg ttttyy ffoorr tthhee bbaacckkeenndd sseerrvveerr **//
ddbbNNaammee == ""tteemmppllaattee11"";;
//** mmaakkee aa ccoonnnneeccttiioonn ttoo tthhee ddaattaabbaassee **//
ccoonnnn == PPQQsseettddbb((ppgghhoosstt,, ppggppoorrtt,, ppggooppttiioonnss,, ppggttttyy,, ddbbNNaammee));;
//** cchheecckk ttoo sseeee tthhaatt tthhee bbaacckkeenndd ccoonnnneeccttiioonn wwaass ssuucccceessssffuullllyy mmaaddee **//
iiff ((PPQQssttaattuuss((ccoonnnn)) ==== CCOONNNNEECCTTIIOONN__BBAADD)) {{
ffpprriinnttff((ssttddeerrrr,,""CCoonnnneeccttiioonn ttoo ddaattaabbaassee ''%%ss'' ffaaiilleedd..00,, ddbbNNaammee));;
ffpprriinnttff((ssttddeerrrr,,""%%ss"",,PPQQeerrrroorrMMeessssaaggee((ccoonnnn))));;
eexxiitt__nniicceellyy((ccoonnnn));;
}}
//** ddeebbuugg == ffooppeenn((""//ttmmpp//ttrraaccee..oouutt"",,""ww""));; **//
//** PPQQttrraaccee((ccoonnnn,, ddeebbuugg));; **//
//** ssttaarrtt aa ttrraannssaaccttiioonn bblloocckk **//
5588
rreess == PPQQeexxeecc((ccoonnnn,,""BBEEGGIINN""));;
iiff ((PPQQrreessuullttSSttaattuuss((rreess)) !!== PPGGRREESS__CCOOMMMMAANNDD__OOKK)) {{
ffpprriinnttff((ssttddeerrrr,,""BBEEGGIINN ccoommmmaanndd ffaaiilleedd00));;
PPQQcclleeaarr((rreess));;
eexxiitt__nniicceellyy((ccoonnnn));;
}}
//** sshhoouulldd PPQQcclleeaarr PPGGrreessuulltt wwhheenneevveerr iitt iiss nnoo lloonnggeerr nneeeeddeedd ttoo aavvooiidd
mmeemmoorryy lleeaakkss **//
PPQQcclleeaarr((rreess));;
//** ffeettcchh iinnssttaanncceess ffrroomm tthhee ppgg__ddaattaabbaassee,, tthhee ssyysstteemm ccaattaalloogg ooff ddaattaabbaasseess**//
rreess == PPQQeexxeecc((ccoonnnn,,""DDEECCLLAARREE mmyyppoorrttaall CCUURRSSOORR FFOORR sseelleecctt ** ffrroomm ppgg__ddaattaabbaassee""));;
iiff ((PPQQrreessuullttSSttaattuuss((rreess)) !!== PPGGRREESS__CCOOMMMMAANNDD__OOKK)) {{
ffpprriinnttff((ssttddeerrrr,,""DDEECCLLAARREE CCUURRSSOORR ccoommmmaanndd ffaaiilleedd00));;
PPQQcclleeaarr((rreess));;
eexxiitt__nniicceellyy((ccoonnnn));;
}}
PPQQcclleeaarr((rreess));;
rreess == PPQQeexxeecc((ccoonnnn,,""FFEETTCCHH AALLLL iinn mmyyppoorrttaall""));;
iiff ((PPQQrreessuullttSSttaattuuss((rreess)) !!== PPGGRREESS__TTUUPPLLEESS__OOKK)) {{
ffpprriinnttff((ssttddeerrrr,,""FFEETTCCHH AALLLL ccoommmmaanndd ddiiddnn''tt rreettuurrnn ttuupplleess pprrooppeerrllyy00));;
PPQQcclleeaarr((rreess));;
eexxiitt__nniicceellyy((ccoonnnn));;
}}
//** ffiirrsstt,, pprriinntt oouutt tthhee aattttrriibbuuttee nnaammeess **//
nnFFiieellddss == PPQQnnffiieellddss((rreess));;
ffoorr ((ii==00;; ii << nnFFiieellddss;; ii++++)) {{
pprriinnttff((""%%--1155ss"",,PPQQffnnaammee((rreess,,ii))));;
}}
pprriinnttff((""00));;
//** nneexxtt,, pprriinntt oouutt tthhee iinnssttaanncceess **//
ffoorr ((ii==00;; ii << PPQQnnttuupplleess((rreess));; ii++++)) {{
ffoorr ((jj==00 ;; jj << nnFFiieellddss;; jj++++)) {{
pprriinnttff((""%%--1155ss"",, PPQQggeettvvaalluuee((rreess,,ii,,jj))));;
}}
pprriinnttff((""00));;
}}
PPQQcclleeaarr((rreess));;
//** cclloossee tthhee ppoorrttaall **//
rreess == PPQQeexxeecc((ccoonnnn,, ""CCLLOOSSEE mmyyppoorrttaall""));;
PPQQcclleeaarr((rreess));;
//** eenndd tthhee ttrraannssaaccttiioonn **//
rreess == PPQQeexxeecc((ccoonnnn,, ""EENNDD""));;
PPQQcclleeaarr((rreess));;
//** cclloossee tthhee ccoonnnneeccttiioonn ttoo tthhee ddaattaabbaassee aanndd cclleeaannuupp **//
PPQQffiinniisshh((ccoonnnn));;
5599
//** ffcclloossee((ddeebbuugg));; **//
}}
6600
1122..1100..22.. SSaammppllee PPrrooggrraamm 22
//**
** tteessttlliibbppqq22..cc
** TTeesstt ooff tthhee aassyynncchhrroonnoouuss nnoottiiffiiccaattiioonn iinntteerrffaaccee
**
ppooppuullaattee aa ddaattaabbaassee wwiitthh tthhee ffoolllloowwiinngg::
CCRREEAATTEE TTAABBLLEE TTBBLL11 ((ii iinntt44));;
CCRREEAATTEE TTAABBLLEE TTBBLL22 ((ii iinntt44));;
CCRREEAATTEE RRUULLEE rr11 AASS OONN IINNSSEERRTT TTOO TTBBLL11 DDOO [[IINNSSEERRTT IINNTTOO TTBBLL22 vvaalluueess ((nneeww..ii));; NNOOTTIIFFYY TTBBLL22]];;
** TThheenn ssttaarrtt uupp tthhiiss pprrooggrraamm
** AAfftteerr tthhee pprrooggrraamm hhaass bbeegguunn,, ddoo
IINNSSEERRTT IINNTTOO TTBBLL11 vvaalluueess ((1100));;
**
**
**//
##iinncclluuddee <>
##iinncclluuddee ""lliibbppqq--ffee..hh""
vvooiidd eexxiitt__nniicceellyy((PPGGccoonnnn** ccoonnnn))
{{
PPQQffiinniisshh((ccoonnnn));;
eexxiitt((11));;
}}
mmaaiinn(())
{{
cchhaarr **ppgghhoosstt,, **ppggppoorrtt,, **ppggooppttiioonnss,, **ppggttttyy;;
cchhaarr** ddbbNNaammee;;
iinntt nnFFiieellddss;;
iinntt ii,,jj;;
PPGGccoonnnn** ccoonnnn;;
PPGGrreessuulltt** rreess;;
PPGGnnoottiiffyy** nnoottiiffyy;;
//** bbeeggiinn,, bbyy sseettttiinngg tthhee ppaarraammeetteerrss ffoorr aa bbaacckkeenndd ccoonnnneeccttiioonn
iiff tthhee ppaarraammeetteerrss aarree nnuullll,, tthheenn tthhee ssyysstteemm wwiillll ttrryy ttoo uussee
rreeaassoonnaabbllee ddeeffaauullttss bbyy llooookkiinngg uupp eennvviirroonnmmeenntt vvaarriiaabblleess
oorr,, ffaaiilliinngg tthhaatt,, uussiinngg hhaarrddwwiirreedd ccoonnssttaannttss **//
ppgghhoosstt == NNUULLLL;; //** hhoosstt nnaammee ooff tthhee bbaacckkeenndd sseerrvveerr **//
ppggppoorrtt == NNUULLLL;; //** ppoorrtt ooff tthhee bbaacckkeenndd sseerrvveerr **//
ppggooppttiioonnss == NNUULLLL;; //** ssppeecciiaall ooppttiioonnss ttoo ssttaarrtt uupp tthhee bbaacckkeenndd sseerrvveerr **//
ppggttttyy == NNUULLLL;; //** ddeebbuuggggiinngg ttttyy ffoorr tthhee bbaacckkeenndd sseerrvveerr **//
ddbbNNaammee == ggeetteennvv((""UUSSEERR""));; //** cchhaannggee tthhiiss ttoo tthhee nnaammee ooff yyoouurr tteesstt ddaattaabbaassee**//
//** mmaakkee aa ccoonnnneeccttiioonn ttoo tthhee ddaattaabbaassee **//
ccoonnnn == PPQQsseettddbb((ppgghhoosstt,, ppggppoorrtt,, ppggooppttiioonnss,, ppggttttyy,, ddbbNNaammee));;
6611
//** cchheecckk ttoo sseeee tthhaatt tthhee bbaacckkeenndd ccoonnnneeccttiioonn wwaass ssuucccceessssffuullllyy mmaaddee **//
iiff ((PPQQssttaattuuss((ccoonnnn)) ==== CCOONNNNEECCTTIIOONN__BBAADD)) {{
ffpprriinnttff((ssttddeerrrr,,""CCoonnnneeccttiioonn ttoo ddaattaabbaassee ''%%ss'' ffaaiilleedd..00,, ddbbNNaammee));;
ffpprriinnttff((ssttddeerrrr,,""%%ss"",,PPQQeerrrroorrMMeessssaaggee((ccoonnnn))));;
eexxiitt__nniicceellyy((ccoonnnn));;
}}
rreess == PPQQeexxeecc((ccoonnnn,, ""LLIISSTTEENN TTBBLL22""));;
iiff ((PPQQrreessuullttSSttaattuuss((rreess)) !!== PPGGRREESS__CCOOMMMMAANNDD__OOKK)) {{
ffpprriinnttff((ssttddeerrrr,,""LLIISSTTEENN ccoommmmaanndd ffaaiilleedd00));;
PPQQcclleeaarr((rreess));;
eexxiitt__nniicceellyy((ccoonnnn));;
}}
//** sshhoouulldd PPQQcclleeaarr PPGGrreessuulltt wwhheenneevveerr iitt iiss nnoo lloonnggeerr nneeeeddeedd ttoo aavvooiidd
mmeemmoorryy lleeaakkss **//
PPQQcclleeaarr((rreess));;
wwhhiillee ((11)) {{
//** aassyynncc nnoottiiffiiccaattiioonn oonnllyy ccoommee bbaacckk aass aa rreessuulltt ooff aa qquueerryy**//
//** wwee ccaann sseenndd eemmppttyy qquueerriieess **//
rreess == PPQQeexxeecc((ccoonnnn,, "" ""));;
//** pprriinnttff((""rreess-->>ssttaattuuss == %%ss00,, ppggrreessSSttaattuuss[[PPQQrreessuullttSSttaattuuss((rreess))]]));; **//
//** cchheecckk ffoorr aassyynncchhrroonnoouuss rreettuurrnnss **//
nnoottiiffyy == PPQQnnoottiiffiieess((ccoonnnn));;
iiff ((nnoottiiffyy)) {{
ffpprriinnttff((ssttddeerrrr,,
""AASSYYNNCC NNOOTTIIFFYY ooff ''%%ss'' ffrroomm bbaacckkeenndd ppiidd ''%%dd'' rreecceeiivveedd00,,
nnoottiiffyy-->>rreellnnaammee,, nnoottiiffyy-->>bbee__ppiidd));;
ffrreeee((nnoottiiffyy));;
bbrreeaakk;;
}}
PPQQcclleeaarr((rreess));;
}}
//** cclloossee tthhee ccoonnnneeccttiioonn ttoo tthhee ddaattaabbaassee aanndd cclleeaannuupp **//
PPQQffiinniisshh((ccoonnnn));;
}}
6622
1122..1100..33.. SSaammppllee PPrrooggrraamm 33
//**
** tteessttlliibbppqq33..cc
** TTeesstt tthhee CC vveerrssiioonn ooff LLIIBBPPQQ,, tthhee PPOOSSTTGGRREESS ffrroonntteenndd lliibbrraarryy..
** tteessttss tthhee bbiinnaarryy ccuurrssoorr iinntteerrffaaccee
**
**
**
ppooppuullaattee aa ddaattaabbaassee bbyy ddooiinngg tthhee ffoolllloowwiinngg::
CCRREEAATTEE TTAABBLLEE tteesstt11 ((ii iinntt44,, dd ffllooaatt44,, pp ppoollyyggoonn));;
IINNSSEERRTT IINNTTOO tteesstt11 vvaalluueess ((11,, 33..556677,, ''((33..00,, 44..00,, 11..00,, 22..00))''::::ppoollyyggoonn));;
IINNSSEERRTT IINNTTOO tteesstt11 vvaalluueess ((22,, 8899..0055,, ''((44..00,, 33..00,, 22..00,, 11..00))''::::ppoollyyggoonn));;
tthhee eexxppeecctteedd oouuttppuutt iiss::
ttuuppllee 00:: ggoott
ii == ((44 bbyytteess)) 11,,
dd == ((44 bbyytteess)) 33..556677000000,,
pp == ((44 bbyytteess)) 22 ppooiinnttss bboouunnddbbooxx == ((hhii==33..000000000000//44..000000000000,, lloo == 11..000000000000,,22..000000000000))
ttuuppllee 11:: ggoott
ii == ((44 bbyytteess)) 22,,
dd == ((44 bbyytteess)) 8899..005500000033,,
pp == ((44 bbyytteess)) 22 ppooiinnttss bboouunnddbbooxx == ((hhii==44..000000000000//33..000000000000,, lloo == 22..000000000000,,11..000000000000))
**
**//
##iinncclluuddee <>
##iinncclluuddee ""lliibbppqq--ffee..hh""
##iinncclluuddee ""uuttiillss//ggeeoo--ddeeccllss..hh"" //** ffoorr tthhee PPOOLLYYGGOONN ttyyppee **//
vvooiidd eexxiitt__nniicceellyy((PPGGccoonnnn** ccoonnnn))
{{
PPQQffiinniisshh((ccoonnnn));;
eexxiitt((11));;
}}
mmaaiinn(())
{{
cchhaarr **ppgghhoosstt,, **ppggppoorrtt,, **ppggooppttiioonnss,, **ppggttttyy;;
cchhaarr** ddbbNNaammee;;
iinntt nnFFiieellddss;;
iinntt ii,,jj;;
iinntt ii__ffnnuumm,, dd__ffnnuumm,, pp__ffnnuumm;;
PPGGccoonnnn** ccoonnnn;;
PPGGrreessuulltt** rreess;;
//** bbeeggiinn,, bbyy sseettttiinngg tthhee ppaarraammeetteerrss ffoorr aa bbaacckkeenndd ccoonnnneeccttiioonn
iiff tthhee ppaarraammeetteerrss aarree nnuullll,, tthheenn tthhee ssyysstteemm wwiillll ttrryy ttoo uussee
rreeaassoonnaabbllee ddeeffaauullttss bbyy llooookkiinngg uupp eennvviirroonnmmeenntt vvaarriiaabblleess
6633
oorr,, ffaaiilliinngg tthhaatt,, uussiinngg hhaarrddwwiirreedd ccoonnssttaannttss **//
ppgghhoosstt == NNUULLLL;; //** hhoosstt nnaammee ooff tthhee bbaacckkeenndd sseerrvveerr **//
ppggppoorrtt == NNUULLLL;; //** ppoorrtt ooff tthhee bbaacckkeenndd sseerrvveerr **//
ppggooppttiioonnss == NNUULLLL;; //** ssppeecciiaall ooppttiioonnss ttoo ssttaarrtt uupp tthhee bbaacckkeenndd sseerrvveerr **//
ppggttttyy == NNUULLLL;; //** ddeebbuuggggiinngg ttttyy ffoorr tthhee bbaacckkeenndd sseerrvveerr **//
ddbbNNaammee == ggeetteennvv((""UUSSEERR""));; //** cchhaannggee tthhiiss ttoo tthhee nnaammee ooff yyoouurr tteesstt ddaattaabbaassee**//
//** mmaakkee aa ccoonnnneeccttiioonn ttoo tthhee ddaattaabbaassee **//
ccoonnnn == PPQQsseettddbb((ppgghhoosstt,, ppggppoorrtt,, ppggooppttiioonnss,, ppggttttyy,, ddbbNNaammee));;
//** cchheecckk ttoo sseeee tthhaatt tthhee bbaacckkeenndd ccoonnnneeccttiioonn wwaass ssuucccceessssffuullllyy mmaaddee **//
iiff ((PPQQssttaattuuss((ccoonnnn)) ==== CCOONNNNEECCTTIIOONN__BBAADD)) {{
ffpprriinnttff((ssttddeerrrr,,""CCoonnnneeccttiioonn ttoo ddaattaabbaassee ''%%ss'' ffaaiilleedd..00,, ddbbNNaammee));;
ffpprriinnttff((ssttddeerrrr,,""%%ss"",,PPQQeerrrroorrMMeessssaaggee((ccoonnnn))));;
eexxiitt__nniicceellyy((ccoonnnn));;
}}
//** ssttaarrtt aa ttrraannssaaccttiioonn bblloocckk **//
rreess == PPQQeexxeecc((ccoonnnn,,""BBEEGGIINN""));;
iiff ((PPQQrreessuullttSSttaattuuss((rreess)) !!== PPGGRREESS__CCOOMMMMAANNDD__OOKK)) {{
ffpprriinnttff((ssttddeerrrr,,""BBEEGGIINN ccoommmmaanndd ffaaiilleedd00));;
PPQQcclleeaarr((rreess));;
eexxiitt__nniicceellyy((ccoonnnn));;
}}
//** sshhoouulldd PPQQcclleeaarr PPGGrreessuulltt wwhheenneevveerr iitt iiss nnoo lloonnggeerr nneeeeddeedd ttoo aavvooiidd
mmeemmoorryy lleeaakkss **//
PPQQcclleeaarr((rreess));;
//** ffeettcchh iinnssttaanncceess ffrroomm tthhee ppgg__ddaattaabbaassee,, tthhee ssyysstteemm ccaattaalloogg ooff ddaattaabbaasseess**//
rreess == PPQQeexxeecc((ccoonnnn,,""DDEECCLLAARREE mmyyccuurrssoorr BBIINNAARRYY CCUURRSSOORR FFOORR sseelleecctt ** ffrroomm tteesstt11""));;
iiff ((PPQQrreessuullttSSttaattuuss((rreess)) !!== PPGGRREESS__CCOOMMMMAANNDD__OOKK)) {{
ffpprriinnttff((ssttddeerrrr,,""DDEECCLLAARREE CCUURRSSOORR ccoommmmaanndd ffaaiilleedd00));;
PPQQcclleeaarr((rreess));;
eexxiitt__nniicceellyy((ccoonnnn));;
}}
PPQQcclleeaarr((rreess));;
rreess == PPQQeexxeecc((ccoonnnn,,""FFEETTCCHH AALLLL iinn mmyyccuurrssoorr""));;
iiff ((PPQQrreessuullttSSttaattuuss((rreess)) !!== PPGGRREESS__TTUUPPLLEESS__OOKK)) {{
ffpprriinnttff((ssttddeerrrr,,""FFEETTCCHH AALLLL ccoommmmaanndd ddiiddnn''tt rreettuurrnn ttuupplleess pprrooppeerrllyy00));;
PPQQcclleeaarr((rreess));;
eexxiitt__nniicceellyy((ccoonnnn));;
}}
ii__ffnnuumm == PPQQffnnuummbbeerr((rreess,,""ii""));;
dd__ffnnuumm == PPQQffnnuummbbeerr((rreess,,""dd""));;
pp__ffnnuumm == PPQQffnnuummbbeerr((rreess,,""pp""));;
ffoorr ((ii==00;;ii<<33;;ii++++)) {{
pprriinnttff((""ttyyppee[[%%dd]] == %%dd,, ssiizzee[[%%dd]] == %%dd00,,
ii,, PPQQffttyyppee((rreess,,ii)),,
ii,, PPQQffssiizzee((rreess,,ii))));;
6644
}}
ffoorr ((ii==00;; ii << PPQQnnttuupplleess((rreess));; ii++++)) {{
iinntt **iivvaall;;
ffllooaatt **ddvvaall;;
iinntt pplleenn;;
PPOOLLYYGGOONN** ppvvaall;;
//** wwee hhaarrdd--wwiirree tthhiiss ttoo tthhee 33 ffiieellddss wwee kknnooww aabboouutt **//
iivvaall == ((iinntt**))PPQQggeettvvaalluuee((rreess,,ii,,ii__ffnnuumm));;
ddvvaall == ((ffllooaatt**))PPQQggeettvvaalluuee((rreess,,ii,,dd__ffnnuumm));;
pplleenn == PPQQggeettlleennggtthh((rreess,,ii,,pp__ffnnuumm));;
//** pplleenn ddooeessnn''tt iinncclluuddee tthhee lleennggtthh ffiieelldd ssoo nneeeedd ttoo iinnccrreemmeenntt bbyy VVAARRHHDDSSZZ**//
ppvvaall == ((PPOOLLYYGGOONN**)) mmaalllloocc((pplleenn ++ VVAARRHHDDRRSSZZ));;
ppvvaall-->>ssiizzee == pplleenn;;
mmeemmmmoovvee((((cchhaarr**))&&ppvvaall-->>nnppttss,, PPQQggeettvvaalluuee((rreess,,ii,,pp__ffnnuumm)),, pplleenn));;
pprriinnttff((""ttuuppllee %%dd:: ggoott00,, ii));;
pprriinnttff(("" ii == ((%%dd bbyytteess)) %%dd,,00,,
PPQQggeettlleennggtthh((rreess,,ii,,ii__ffnnuumm)),, **iivvaall));;
pprriinnttff(("" dd == ((%%dd bbyytteess)) %%ff,,00,,
PPQQggeettlleennggtthh((rreess,,ii,,dd__ffnnuumm)),, **ddvvaall));;
pprriinnttff(("" pp == ((%%dd bbyytteess)) %%dd ppooiinnttss bboouunnddbbooxx == ((hhii==%%ff//%%ff,, lloo == %%ff,,%%ff))00,,
PPQQggeettlleennggtthh((rreess,,ii,,dd__ffnnuumm)),,
ppvvaall-->>nnppttss,,
ppvvaall-->>bboouunnddbbooxx..xxhh,,
ppvvaall-->>bboouunnddbbooxx..yyhh,,
ppvvaall-->>bboouunnddbbooxx..xxll,,
ppvvaall-->>bboouunnddbbooxx..yyll));;
}}
PPQQcclleeaarr((rreess));;
//** cclloossee tthhee ppoorrttaall **//
rreess == PPQQeexxeecc((ccoonnnn,, ""CCLLOOSSEE mmyyccuurrssoorr""));;
PPQQcclleeaarr((rreess));;
//** eenndd tthhee ttrraannssaaccttiioonn **//
rreess == PPQQeexxeecc((ccoonnnn,, ""EENNDD""));;
PPQQcclleeaarr((rreess));;
//** cclloossee tthhee ccoonnnneeccttiioonn ttoo tthhee ddaattaabbaassee aanndd cclleeaannuupp **//
PPQQffiinniisshh((ccoonnnn));;
}}
6655
________________________________________________________________________________________________________________________
1133.. LLAARRGGEE OOBBJJEECCTTSS
________________________________________________________________________________________________________________________
In POSTGRES, data values are stored in tuples and indi-
vidual tuples cannot span data pages. Since the size of
a data page is 8192 bytes, the upper limit on the size
of a data value is relatively low. To support the stor-
age of larger atomic values, POSTGRES provides a large
object interface. This interface provides file-
oriented access to user data that has been declared to
be a large type.
This section describes the implementation and the pro-
grammatic and query language interfaces to POSTGRES
large object data.
1133..11.. HHiissttoorriiccaall NNoottee
Originally, POSTGRES 4.2 supports three standard imple-
mentations of large objects: as files external to POST-
GRES, as UNIX files managed by POSTGRES, and as data
stored within the POSTGRES database. It causes consid-
erable confusion among users. As a result, we only sup-
port large objects as data stored within the POSTGRES
database in POSTGRES95. Even though is is slower to
access, it provides stricter data integrity and time
travel. For historical reasons, they are called Inver-
sion large objects. (We will use Inversion and large
objects interchangeably to mean the same thing in this
section.)
1133..22.. IInnvveerrssiioonn LLaarrggee OObbjjeeccttss
The Inversion large object implementation breaks large
objects up into "chunks" and stores the chunks in
tuples in the database. A B-tree index guarantees fast
searches for the correct chunk number when doing random
access reads and writes.
1133..33.. LLaarrggee OObbjjeecctt IInntteerrffaacceess
The facilities POSTGRES provides to access large
objects, both in the backend as part of user-defined
functions or the front end as part of an application
using the interface, are described below. (For users
familiar with POSTGRES 4.2, POSTGRES95 has a new set of
functions providing a more coherent interface. The
interface is the same for dynamically-loaded C func-
tions as well as for .
The POSTGRES large object interface is modeled after
the UNIX file system interface, with analogues of
_o_p_e_n(2), _r_e_a_d(2), _w_r_i_t_e(2), _l_s_e_e_k(2), etc. User func-
tions call these routines to retrieve only the data of
6666
interest from a large object. For example, if a large
object type called _m_u_g_s_h_o_t existed that stored pho-
tographs of faces, then a function called _b_e_a_r_d could
be declared on _m_u_g_s_h_o_t data. _B_e_a_r_d could look at the
lower third of a photograph, and determine the color of
the beard that appeared there, if any. The entire
large object value need not be buffered, or even exam-
ined, by the _b_e_a_r_d function.
Large objects may be accessed from dynamically-loaded C
functions or database client programs that link the
library. POSTGRES provides a set of routines that sup-
port opening, reading, writing, closing, and seeking on
large objects.
1133..33..11.. CCrreeaattiinngg aa LLaarrggee OObbjjeecctt
The routine
OOiidd lloo__ccrreeaatt((PPGGccoonnnn **ccoonnnn,, iinntt mmooddee))
creates a new large object. The _m_o_d_e is a bitmask
describing several different attributes of the new
object. The symbolic constants listed here are defined
in
//uussrr//llooccaall//ppoossttggrreess9955//ssrrcc//bbaacckkeenndd//lliibbppqq//lliibbppqq--ffss..hh
The access type (read, write, or both) is controlled by
OR ing together the bits INV_READ and INV_WRITE. If
the large object should be archived -- that is, if his-
torical versions of it should be moved periodically to
a special archive relation -- then the INV_ARCHIVE bit
should be set. The low-order sixteen bits of _m_a_s_k are
the storage manager number on which the large object
should reside. For sites other than Berkeley, these
bits should always be zero.
The commands below create an (Inversion) large object:
iinnvv__ooiidd == lloo__ccrreeaatt((IINNVV__RREEAADD||IINNVV__WWRRIITTEE||IINNVV__AARRCCHHIIVVEE));;
1133..33..22.. IImmppoorrttiinngg aa LLaarrggee OObbjjeecctt To import a UNIX file as
a large object, call
OOiidd
lloo__iimmppoorrtt((PPGGccoonnnn **ccoonnnn,, tteexxtt **ffiilleennaammee))
The _f_i_l_e_n_a_m_e argument specifies the UNIX pathname of
the file to be imported as a large object.
1133..33..33.. EExxppoorrttiinngg aa LLaarrggee OObbjjeecctt To export a large object
into UNIX file, call
iinntt
6677
lloo__eexxppoorrtt((PPGGccoonnnn **ccoonnnn,, OOiidd lloobbjjIIdd,, tteexxtt **ffiilleennaammee))
The _l_o_b_j_I_d argument specifies the Oid of the large
object to export and the _f_i_l_e_n_a_m_e argument specifies
the UNIX pathname of the file.
1133..33..44.. OOppeenniinngg aann EExxiissttiinngg LLaarrggee OObbjjeecctt
To open an existing large object, call
iinntt
lloo__ooppeenn((PPGGccoonnnn **ccoonnnn,, OOiidd lloobbjjIIdd,, iinntt mmooddee,, ......))
The _l_o_b_j_I_d argument specifies the Oid of the large
object to open. The mode bits control whether the
object is opened for reading INV_READ), writing or
both.
A large object cannot be opened before it is created.
lloo__ooppeenn returns a large object descriptor for later use
in lloo__rreeaadd, lloo__wwrriittee, lloo__llsseeeekk, lloo__tteellll, and lloo__cclloossee.
1133..33..55.. WWrriittiinngg DDaattaa ttoo aa LLaarrggee OObbjjeecctt
The routine
iinntt
lloo__wwrriittee((PPGGccoonnnn **ccoonnnn,, iinntt ffdd,, cchhaarr **bbuuff,, iinntt lleenn))
writes _l_e_n bytes from _b_u_f to large object _f_d. The _f_d
argument must have been returned by a previous _l_o___o_p_e_n.
The number of bytes actually written is returned. In
the event of an error, the return value is negative.
1133..33..66.. SSeeeekkiinngg oonn aa LLaarrggee OObbjjeecctt
To change the current read or write location on a large
object, call
iinntt
lloo__llsseeeekk((PPGGccoonnnn **ccoonnnn,, iinntt ffdd,, iinntt ooffffsseett,, iinntt wwhheennccee))
This routine moves the current location pointer for the
large object described by _f_d to the new location speci-
fied by _o_f_f_s_e_t. The valid values for .i whence are
SSEEEEKK__SSEETT SSEEEEKK__CCUURR and SSEEEEKK__EENNDD..
1133..33..77.. CClloossiinngg aa LLaarrggee OObbjjeecctt DDeessccrriippttoorr
A large object may be closed by calling
iinntt
lloo__cclloossee((PPGGccoonnnn **ccoonnnn,, iinntt ffdd))
where _f_d is a large object descriptor returned by
_l_o___o_p_e_n. On success, _l_o___c_l_o_s_e returns zero. On error,
the return value is negative.
6688
1133..44.. BBuuiilltt iinn rreeggiisstteerreedd ffuunnccttiioonnss
There are two built-in registered functions, _l_o___i_m_p_o_r_t
and _l_o___e_x_p_o_r_t which are convenient for use in SQL
queries.
Here is an example of there use
CCRREEAATTEE TTAABBLLEE iimmaaggee ((
nnaammee tteexxtt,,
rraasstteerr ooiidd
));;
IINNSSEERRTT IINNTTOO iimmaaggee ((nnaammee,, rraasstteerr))
VVAALLUUEESS ((''bbeeaauuttiiffuull iimmaaggee'',, lloo__iimmppoorrtt((''//eettcc//mmoottdd''))));;
SSEELLEECCTT lloo__eexxppoorrtt((iimmaaggee..rraasstteerr,, ""//ttmmpp//mmoottdd"")) ffrroomm iimmaaggee
WWHHEERREE nnaammee == ''bbeeaauuttiiffuull iimmaaggee'';;
1133..55.. AAcccceessssiinngg LLaarrggee OObbjjeeccttss ffrroomm LLIIBBPPQQ Below is a sam-
ple program which shows how the large object interface
in LIBPQ can be used. Parts of the program are com-
mented out but are left in the source for the readers
benefit. This program can be found in
....//ssrrcc//tteesstt//eexxaammpplleess
Frontend applications which use the large object inter-
face in LIBPQ should include the header file
lliibbppqq//lliibbppqq--ffss..hh and link with the lliibbppqq library.
6699
1133..66.. SSaammppllee PPrrooggrraamm
//**--------------------------------------------------------------------------------------------------------------------------------------------------
**
** tteessttlloo..cc----
** tteesstt uussiinngg llaarrggee oobbjjeeccttss wwiitthh lliibbppqq
**
** CCooppyyrriigghhtt ((cc)) 11999944,, RReeggeennttss ooff tthhee UUnniivveerrssiittyy ooff CCaalliiffoorrnniiaa
**
**
** IIDDEENNTTIIFFIICCAATTIIOONN
** //uussrr//llooccaall//ddeevveell//ppgglliittee//ccvvss//ssrrcc//ddoocc//mmaannuuaall..mmee,,vv 11..1166 11999955//0099//0011 2233::5555::0000 jjoollllyy EExxpp
**
**--------------------------------------------------------------------------------------------------------------------------------------------------
**//
##iinncclluuddee <>
##iinncclluuddee ""lliibbppqq--ffee..hh""
##iinncclluuddee ""lliibbppqq//lliibbppqq--ffss..hh""
##ddeeffiinnee BBUUFFSSIIZZEE 11002244
//**
** iimmppoorrttFFiillee --
** iimmppoorrtt ffiillee ""iinn__ffiilleennaammee"" iinnttoo ddaattaabbaassee aass llaarrggee oobbjjeecctt ""lloobbjjOOiidd""
**
**//
OOiidd iimmppoorrttFFiillee((PPGGccoonnnn **ccoonnnn,, cchhaarr **ffiilleennaammee))
{{
OOiidd lloobbjjIIdd;;
iinntt lloobbjj__ffdd;;
cchhaarr bbuuff[[BBUUFFSSIIZZEE]];;
iinntt nnbbyytteess,, ttmmpp;;
iinntt ffdd;;
//**
** ooppeenn tthhee ffiillee ttoo bbee rreeaadd iinn
**//
ffdd == ooppeenn((ffiilleennaammee,, OO__RRDDOONNLLYY,, 00666666));;
iiff ((ffdd << 00)) {{ //** eerrrroorr **//
ffpprriinnttff((ssttddeerrrr,, ""ccaann''tt ooppeenn uunniixx ffiillee
}}
//**
** ccrreeaattee tthhee llaarrggee oobbjjeecctt
**//
lloobbjjIIdd == lloo__ccrreeaatt((ccoonnnn,, IINNVV__RREEAADD||IINNVV__WWRRIITTEE));;
iiff ((lloobbjjIIdd ==== 00)) {{
ffpprriinnttff((ssttddeerrrr,, ""ccaann''tt ccrreeaattee llaarrggee oobbjjeecctt""));;
}}
lloobbjj__ffdd == lloo__ooppeenn((ccoonnnn,, lloobbjjIIdd,, IINNVV__WWRRIITTEE));;
//**
** rreeaadd iinn ffrroomm tthhee UUnniixx ffiillee aanndd wwrriittee ttoo tthhee iinnvveerrssiioonn ffiillee
7700
**//
wwhhiillee ((((nnbbyytteess == rreeaadd((ffdd,, bbuuff,, BBUUFFSSIIZZEE)))) >> 00)) {{
ttmmpp == lloo__wwrriittee((ccoonnnn,, lloobbjj__ffdd,, bbuuff,, nnbbyytteess));;
iiff ((ttmmpp << nnbbyytteess)) {{
ffpprriinnttff((ssttddeerrrr,, ""eerrrroorr wwhhiillee rreeaaddiinngg
}}
}}
((vvooiidd)) cclloossee((ffdd));;
((vvooiidd)) lloo__cclloossee((ccoonnnn,, lloobbjj__ffdd));;
rreettuurrnn lloobbjjIIdd;;
}}
vvooiidd ppiicckkoouutt((PPGGccoonnnn **ccoonnnn,, OOiidd lloobbjjIIdd,, iinntt ssttaarrtt,, iinntt lleenn))
{{
iinntt lloobbjj__ffdd;;
cchhaarr** bbuuff;;
iinntt nnbbyytteess;;
iinntt nnrreeaadd;;
lloobbjj__ffdd == lloo__ooppeenn((ccoonnnn,, lloobbjjIIdd,, IINNVV__RREEAADD));;
iiff ((lloobbjj__ffdd << 00)) {{
ffpprriinnttff((ssttddeerrrr,,""ccaann''tt ooppeenn llaarrggee oobbjjeecctt %%dd"",,
lloobbjjIIdd));;
}}
lloo__llsseeeekk((ccoonnnn,, lloobbjj__ffdd,, ssttaarrtt,, SSEEEEKK__SSEETT));;
bbuuff == mmaalllloocc((lleenn++11));;
nnrreeaadd == 00;;
wwhhiillee ((lleenn -- nnrreeaadd >> 00)) {{
nnbbyytteess == lloo__rreeaadd((ccoonnnn,, lloobbjj__ffdd,, bbuuff,, lleenn -- nnrreeaadd));;
bbuuff[[nnbbyytteess]] == '' '';;
ffpprriinnttff((ssttddeerrrr,,"">>>>>> %%ss"",, bbuuff));;
nnrreeaadd ++== nnbbyytteess;;
}}
ffpprriinnttff((ssttddeerrrr,,""00));;
lloo__cclloossee((ccoonnnn,, lloobbjj__ffdd));;
}}
vvooiidd oovveerrwwrriittee((PPGGccoonnnn **ccoonnnn,, OOiidd lloobbjjIIdd,, iinntt ssttaarrtt,, iinntt lleenn))
{{
iinntt lloobbjj__ffdd;;
cchhaarr** bbuuff;;
iinntt nnbbyytteess;;
iinntt nnwwrriitttteenn;;
iinntt ii;;
lloobbjj__ffdd == lloo__ooppeenn((ccoonnnn,, lloobbjjIIdd,, IINNVV__RREEAADD));;
iiff ((lloobbjj__ffdd << 00)) {{
ffpprriinnttff((ssttddeerrrr,,""ccaann''tt ooppeenn llaarrggee oobbjjeecctt %%dd"",,
lloobbjjIIdd));;
7711
}}
lloo__llsseeeekk((ccoonnnn,, lloobbjj__ffdd,, ssttaarrtt,, SSEEEEKK__SSEETT));;
bbuuff == mmaalllloocc((lleenn++11));;
ffoorr ((ii==00;;ii<