You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Andrus Adamchik <an...@objectstyle.org> on 2013/07/03 08:32:46 UTC

Re: PostgreSQL Stored Procedure only returning one row

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 <gr...@gmail.com> 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.
>