Table of Contents
PostgreSQL™ is an extensible database system. You can add your own functions to the server, which can then be called from queries, or even add your own data types. As these are facilities unique to PostgreSQL™, we support them from Java, with a set of extension API's. Some features within the core of the standard driver actually use these extensions to implement Large Objects, etc.
To access some of the extensions, you need to use some extra methods in the org.postgresql.PGConnection class. In this case, you would need to case the return value of Driver.getConnection(). For example:
Connection db = Driver.getConnection(url, username, password); // ... // later on Fastpath fp = ((org.postgresql.PGConnection)db).getFastpathAPI();
public class PGConnection
These are the extra methods used to gain access to PostgreSQL™'s extensions.
public Fastpath getFastpathAPI() throws SQLException
This returns the fast-path API for the current connection. It is primarily used by the Large Object API.
The best way to use this is as follows:
import org.postgresql.fastpath.*; ... Fastpath fp = ((org.postgresql.PGConnection)myconn).getFastpathAPI();where myconn is an open Connection to PostgreSQL™.
Returns: Fastpath object allowing access to functions on the PostgreSQL™ server.
Throws: SQLException by Fastpath when initializing for first time
public LargeObjectManager getLargeObjectAPI() throws SQLExceptionThis returns the Large Object API for the current connection.
The best way to use this is as follows:
import org.postgresql.largeobject.*; ... LargeObjectManager lo = ((org.postgresql.PGConnection)myconn).getLargeObjectAPI();where myconn is an open Connection to PostgreSQL™.
Returns: LargeObject object that implements the API
Throws: SQLException by LargeObject when initializing for first time
public void addDataType(String type, String name)This allows client code to add a handler for one of PostgreSQL™'s more unique data types. Normally, a data type not known by the driver is returned by ResultSet.getObject() as a PGobject instance. This method allows you to write a class that extends PGobject, and tell the driver the type name, and class name to use. The down side to this, is that you must call this method each time a connection is made.
The best way to use this is as follows:
...
((org.postgresql.PGConnection)myconn).addDataType("mytype","my.class.name");
...
where myconn is an open Connection to
PostgreSQL™. The handling class must
extend org.postgresql.util.PGobject.
public class Fastpath extends Object java.lang.Object | +----org.postgresql.fastpath.Fastpath
Fastpath is an API that exists within the libpq C interface, and allows a client machine to execute a function on the database server. Most client code will not need to use this method, but it is provided because the Large Object API uses it.
To use, you need to import the org.postgresql.fastpath package, using the line:
import org.postgresql.fastpath.*;Then, in your code, you need to get a FastPath object:
Fastpath fp = ((org.postgresql.PGConnection)conn).getFastpathAPI();This will return an instance associated with the database connection that you can use to issue commands. The casing of Connection to org.postgresql.PGConnection is required, as the getFastpathAPI() is an extension method, not part of JDBC. Once you have a Fastpath instance, you can use the fastpath() methods to execute a server function.
See Also: FastpathFastpathArg, LargeObject
public Object fastpath(int fnid,
boolean resulttype,
FastpathArg args[]) throws SQLException
Send a function call to the PostgreSQL™ server.
Parameters: fnid - Function id resulttype - True if the result is an integer, false for other results args - FastpathArguments to pass to fast-path call
Returns: null if no data, Integer if an integer result, or byte[] otherwise
public Object fastpath(String name,
boolean resulttype,
FastpathArg args[]) throws SQLException
Send a function call to the PostgreSQL™ server by name.
The mapping for the procedure name to function id needs to exist, usually to an earlier call to addfunction(). This is the preferred method to call, as function id's can/may change between versions of the server. For an example of how this works, refer to org.postgresql.LargeObject
Parameters: name - Function name resulttype - True if the result is an integer, false for other results args - FastpathArguments to pass to fast-path call
Returns: null if no data, Integer if an integer result, or byte[] otherwise
See Also: LargeObject
public int getInteger(String name,
FastpathArg args[]) throws SQLException
This convenience method assumes that the return value is an Integer
Parameters: name - Function name args - Function arguments
Returns: integer result
Throws: SQLException if a database-access error occurs or no result
public byte[] getData(String name,
FastpathArg args[]) throws SQLException
This convenience method assumes that the return value is binary data.
Parameters: name - Function name args - Function arguments
Returns: byte[] array containing result
Throws: SQLException if a database-access error occurs or no result
public void addFunction(String name,
int fnid)
This adds a function to our look-up table. User code should use the addFunctions method, which is based upon a query, rather than hard coding the OID. The OID for a function is not guaranteed to remain static, even on different servers of the same version.
public void addFunctions(ResultSet rs) throws SQLException
This takes a ResultSet containing two columns. Column 1 contains the function name, Column 2 the OID. It reads the entire ResultSet, loading the values into the function table.
Remember to close() the ResultSet after calling this!
PostgreSQL™ stores the function id's and their corresponding names in the pg_proc table. To speed things up locally, instead of querying each function from that table when required, a Hashtable is used. Also, only the function's required are entered into this table, keeping connection times as fast as possible.
The org.postgresql.LargeObject class performs a query upon its start-up, and passes the returned ResultSet to the addFunctions() method here. Once this has been done, the Large Object API refers to the functions by name.
Do not think that manually converting them to the OIDs will work. OK, they will for now, but they can change during development (there was some discussion about this for V7.0), so this is implemented to prevent any unwarranted headaches in the future.
See Also: LargeObjectManager
public int getID(String name) throws SQLException
This returns the function id associated by its name If addFunction() or addFunctions() have not been called for this name, then an SQLException is thrown.
public class FastpathArg extends Object java.lang.Object | +----org.postgresql.fastpath.FastpathArg
Each fast-path call requires an array of arguments, the number and type dependent on the function being called. This class implements methods needed to provide this capability.
For an example on how to use this, refer to the org.postgresql.LargeObject package.
See Also: Fastpath, LargeObjectManager, LargeObject
public FastpathArg(int value)
Constructs an argument that consists of an integer value
Parameters: value - int value to set
public FastpathArg(byte bytes[])
Constructs an argument that consists of an array of bytes
Parameters: bytes - array to store
public FastpathArg(byte buf[],
int off,
int len)
Constructs an argument that consists of part of a byte array
Parameters:
public FastpathArg(String s)
Constructs an argument that consists of a String.