Thread: c function return not recognized?

I've got an external  C function that I call from a SELECT, for example:

select * from <table> where <cond1> and CFunction(columnName) > 1.0;

The 'CFunction' has been installed into Postgreql using CREATE FUNCTION,
with a single 'text' argument, and returning a float8 (i.e. double).
I can tell that the 'CFunction' is getting called with the correct
argument, because it can be compiled to echo its activities to a file.
Also, if I rewrite the second condition (for example, leaving out the
'>' sign), psql complains that the function returns a float8, not a
boolean value.

The problem is that Postgresql seems to treat the latter condition
as an "fail always".  I can compile the function to return a constant,
say for example, 1.0.  Without the second condition, I always get the
expected set of results.  With the second condition, no results are
returned regardless of the sense of the comparison: >, <, =, <> all
return nothing.

I would appreciate it if someone could help me be able to use an
externally compiled function that returns a result.

TIA!

    -frank

Frank Miles wrote:

> I've got an external  C function that I call from a SELECT, for example:
>
> select * from <table> where <cond1> and CFunction(columnName) > 1.0;
>
> The 'CFunction' has been installed into Postgreql using CREATE FUNCTION,
> with a single 'text' argument, and returning a float8 (i.e. double).

This is becoming a VFAQ, which is nice because it means that the
extensibility features gain popularity (which IMO make postgres stand
out very impressively), but it is disturbing that the message in the
docs and in examples is not getting through.

One can't get anything back from a C function by other means than
reference. One needs to first palloc() the space for the value and
then return the pointer to it. The reason Frank's function behaved as
he describes is likely because the caller treats the return values as
pointers which indeed points to random locations depending on the
input.

There could be other reasons, too, but then again, it is always helpful
to paste a bit of your code so we don't have to speculate about the
exact nature of your problem.

--Gene

Hi,
I'm put several gifa into a table. I did as a exercise:) form psql using:
INSERT INTO images (id, data)
    VALUES (3, lo_import('/usr/local/apache/servlets/images/a.gif'));

but I have a problem with creating Java stream to read these data. Here
serveral lines of code I was using:
PreparedStatement ps=db.prepareStatement("select oid from imag where  id=?");
       ps.setInt(1,1);
       ResultSet rs = ps.executeQuery();
       rs.next();
       InputStream is = rs.getBinaryStream(0);
and nothing happens:( Several messages from exceptions:
[13/12/1999 18:11:04:815 CET] ShowImageServlet1: 0
null
Fastpath: ERROR:  lo_tell: large object descriptor (-1) out of range

Anybody know how to read LargeObjects with java?
btw I read about postgres JDBC extensions LargeObject and
LargeObjectManager and everthing would be great except nobody mentioned
that they are not completely implemented:)
Anybody can send me a small exapmle how to read these data?
mazek

Marcin Mazurek - Multinet SA - Poznan wrote:
> Hi,
> I'm put several gifa into a table. I did as a exercise:) form psql using:
> INSERT INTO images (id, data)
>     VALUES (3, lo_import('/usr/local/apache/servlets/images/a.gif'));

are you sure this lo_import(...) thing in the SQL will work? I have no
idea ...

> but I have a problem with creating Java stream to read these data. Here
> serveral lines of code I was using:
> PreparedStatement ps=db.prepareStatement("select oid from imag where  id=?");
>        ps.setInt(1,1);
>        ResultSet rs = ps.executeQuery();
>        rs.next();
>        InputStream is = rs.getBinaryStream(0);
> and nothing happens:( Several messages from exceptions:
> [13/12/1999 18:11:04:815 CET] ShowImageServlet1: 0
> null
> Fastpath: ERROR:  lo_tell: large object descriptor (-1) out of range

I see two problems with your above Java code snippet:

#1: you SELECT oid FROM imag ... only the OID? Would you not have to select
the data?  A SELECT that would match your insert above would be:

SELECT data FROM images WHERE id=?;


#2: your index in rs.getBinaryStream is zero, but if I recall correctly
the JDBC ResultSet.getXXX(int) functions start counting at 1, not at
0.  So, you should have called:

       PreparedStatement ps=db.prepareStatement(
        "SELECT data FROM image WHERE id=?"); // select data instead of oid
       ps.setInt(1,1);
       ResultSet rs = ps.executeQuery();
       if(rs.next()) {
         InputStream is = rs.getBinaryStream(1);  // start with one (1)

       }

I have never done this BLOB stuff, but you might try whether this
fixes the problem. Also, with the JDBC driver comes a test that
does exactly image storing and retrieval. So you can look there
for a working example.

regards
-Gunther


--
Gunther_Schadow-------------------------------http://aurora.rg.iupui.edu
Regenstrief Institute for Health Care
1050 Wishard Blvd., Indianapolis IN 46202, Phone: (317) 630 7960
schadow@aurora.rg.iupui.edu------------------#include <usual/disclaimer>