My exposure to PostgreSQL is very limited these days. I was recently researching an unrelated PostgreSQL stored proc issue [1], and all I can say is that functions/stored procedures fully work with Cayenne, but mapping them can be quirky and may require some experimentation. Unfortunately, this is not a very specific answer :-/
Andrus [1] http://stackoverflow.com/questions/16921942/porting-apache-cayenne-from-oracle-to-postgresql On Jun 28, 2013, at 1:05 AM, grantdmckenzie <[email protected]> wrote: > Hello all, > > I used Modeler (3.0.2) to "Reegineer Database Schema" from a Postgres 9.1 > database. This database simply has one table, one stored procedure (function > in postgres language) and one type (return set type from the stored > procedure). I was able to successfully map dataobjects to tables and can do > a basic select query on the single table I have. > > When I try to get data via the stored procedure, only 1 row is returned, > when it should be 50. The code snippet is below: > ----- > ObjectContext context = DataContext.createDataContext(); > context.commitChanges(); > > ProcedureQuery query = new ProcedureQuery("w_nearby"); > query.addParameter("$1", -119.82466289999999); > query.addParameter("$2", 34.4413761); > > QueryResponse response = context.performGenericQuery(query); > ----- > > Logger spits out the following: > ----- > Jun 27, 2013 2:59:04 PM org.apache.cayenne.access.QueryLogger logQueryStart > INFO: --- will run 1 query. > Jun 27, 2013 2:59:04 PM org.apache.cayenne.access.QueryLogger logConnect > INFO: Opening connection: jdbc:postgresql://<serverdetails>:5432/<database> > Login: logindetails > Password: ******* > Jun 27, 2013 2:59:04 PM org.apache.cayenne.access.QueryLogger > logConnectSuccess > INFO: +++ Connecting: SUCCESS. > Jun 27, 2013 2:59:04 PM org.apache.cayenne.access.QueryLogger > logBeginTransaction > INFO: --- transaction started. > Jun 27, 2013 2:59:04 PM org.apache.cayenne.access.QueryLogger logQuery > INFO: {? = call public.w_nearby(?, ?, ?, ?, ?, ?)} [bind: > 1:-119.82466289999999, 2:34.4413761, 3:'[OUT]', 4:'[OUT]', 5:'[OUT]', > 6:'[OUT]', 7:'[OUT]'] > Jun 27, 2013 2:59:04 PM org.apache.cayenne.access.QueryLogger logSelectCount > INFO: === returned 1 row. - took 2 ms. > ----- > > My Stored Procedure (Function) looks like this (there are PostGIS calls in > there, but it shouldn't make a difference): > ----- > CREATE OR REPLACE FUNCTION w_nearby(double precision, double precision) > RETURNS SETOF w_loc AS > $BODY$ > DECLARE rec record; > d_lng ALIAS FOR $1; > d_lat ALIAS FOR $2; > > BEGIN > FOR rec IN (select w_id, name, ST_Y(ST_GeomFromText(ST_AsText(geog))), > ST_X(ST_GeomFromText(ST_AsText(geog))), st_distance(geog, > st_geographyfromtext('POINT('||d_lng||' '||d_lat||')')) as dist from poibase > where name is not null and ST_DWithin(geog, > st_geographyfromtext('POINT('||d_lng||' '||d_lat||')'), 1000) order by dist > limit 50) LOOP > RETURN NEXT rec; > END LOOP; > END; > $BODY$ > LANGUAGE plpgsql; > ----- > > Any help would be greatly appreciated. Are there any known issues with > postgresql functions? Thanks. >
