Thread: C Language Stored Procedure Returning No Data
We have a Java client that is using JDBC to communicate with the Postgres database. We have a stored procedure, foo, that is written in C which returns a byte array (bytea) to the Java client. This function takes in a couple of integers and based upon their values can return a byte array or no data. From a C stored procedure, how can I tell Postgres to pass on to the Java client that there is No Data? A zero length byte array or a null value is not the same as No Data.
Thanks for any help that you can give.
The stored procedure is defined as:
CREATE OR REPLACE FUNCTION foo(int8, int8)
RETURNS bytea AS 'foolib.so', 'foo'
LANGUAGE C STRICT;
The C function foo looks like:
PG_FUNCTION_INFO_V1(foo);
Datum foo ( PG_FUNCTION_ARGS)
{
int64 int1 = PG_GETARG_INT64(0);
int64 int2 = PG_GETARG_INT64(1);
bytea *data = NULL;
if ( int1 > 12345 ) {
/* Somehow indicate that there is no data */
/* Doing the following does not tell Java that there is no data; it gets a null value */
PG_RETURN_NULL();
}
/* Some processing */
PG_RETURN_BYTEA_P( data );
}
On Wed, Feb 01, 2006 at 12:56:30PM -0500, bfraci@aol.com wrote:
> From a C stored procedure, how can I tell Postgres to pass on to
> the Java client that there is No Data? A zero length byte array or
> a null value is not the same as No Data.
If you declare the function with "RETURNS bytea" then the function
must return something; if zero-length data and NULL aren't suitable
for indicating no data then you could raise an error and catch that
error in the client. Another possibility would be to make the
function set-returning ("RETURNS SETOF bytea" and some code changes)
and return no rows to indicate no data.
--
Michael Fuhr
On Feb 2, 2006, at 7:17 PM, Michael Fuhr wrote:
> On Wed, Feb 01, 2006 at 12:56:30PM -0500, bfraci@aol.com wrote:
>> From a C stored procedure, how can I tell Postgres to pass on to
>> the Java client that there is No Data? A zero length byte array or
>> a null value is not the same as No Data.
>
> If you declare the function with "RETURNS bytea" then the function
> must return something; if zero-length data and NULL aren't suitable
> for indicating no data then you could raise an error and catch that
> error in the client. Another possibility would be to make the
> function set-returning ("RETURNS SETOF bytea" and some code changes)
> and return no rows to indicate no data.
Another possibility is to log an exception with elog & company.
However that will also have the side effect of rolling back the txn
if you are currently in one.
--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/
On Fri, Feb 03, 2006 at 09:07:48AM -0500, Jeff Trout wrote:
> On Feb 2, 2006, at 7:17 PM, Michael Fuhr wrote:
> >If you declare the function with "RETURNS bytea" then the function
> >must return something; if zero-length data and NULL aren't suitable
> >for indicating no data then you could raise an error and catch that
> >error in the client. Another possibility would be to make the
> >function set-returning ("RETURNS SETOF bytea" and some code changes)
> >and return no rows to indicate no data.
>
> Another possibility is to log an exception with elog & company.
That's what I meant by "raise an error." Incidentally, for user-
visible messages ereport is preferred over "its older cousin" elog
(perhaps ereport is what you meant by "& company").
http://www.postgresql.org/docs/8.1/interactive/error-message-reporting.html
"Therefore, elog should be used only for internal errors and low-level
debug logging. Any message that is likely to be of interest to
ordinary users should go through ereport."
> However that will also have the side effect of rolling back the txn
> if you are currently in one.
In 8.0 and later you could wrap the function call with a savepoint,
explicitly or implicitly (e.g., via an EXCEPTION clause in a PL/pgSQL
function). Exception-handling code in the caller could roll back
to the savepoint if an error is raised.
--
Michael Fuhr