On 02/05/2013 09:01 PM, Ben Madin wrote:
> Thanks Adrian,
>
> On 2013-02-06, at 12:52 , Adrian Klaver <adrian.klaver@gmail.com> wrote:
>
>> On 02/05/2013 08:24 PM, Ben Madin wrote:
>>> The full query is :
>>>
>>> {{{
>>> SELECT rep.id, res8.reportid, round(st_distance_sphere('0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0',
post.the_point::geometry)/1000)as dist
>>> FROM reports rep
>>> LEFT JOIN users u ON rep.link = u.id
>>> LEFT JOIN postcodes post ON u.postcode::integer = post.postcode
>>> LEFT JOIN species spe ON rep.species::text like spe.speccode::text AND spe.synonym = 0
>>> LEFT JOIN results res8 ON res8.reportid = rep.id AND res8.resulttypeid = 108 AND res8.del = false
>>> LEFT JOIN resultlookup rlu8 ON rlu8.resulttypesid = 108 AND rlu8.id = res8.resultvalue::int
>>> WHERE rep.del IS false AND rep.projectid = 51
>>> AND round(st_distance_sphere( '0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0', post.the_point)/1000) < '150'
ANDspe.id = '9465' AND rlu8.id = '935';
>>> }}}
>>>
>>
>>
>> Follow up questions:
>>
>> 1) Where is this query being run from?
>
> It is meant to be being executed in a pl/pgsql function as part of a loop - the rep.id is then used to return the
correspondingrows. This function is working on the dev machine. The query I have appended is produced in the function
asbelow. When I throw the query as above at the psql command line, it works on the dev machine. (but not on the
productionbox). The final part of the function looks like :
So to be clear the querystring below is the query shown above?
That still leaves the issue of why it fails in psql?
At this point I am stumped. Maybe someone else has an idea. There is
always the outside chance I get a light bulb moment, don't hold your
breath:)
>
> {{{
> RAISE NOTICE 'The final query is : %', querystring;
>
> FOR repid, dist IN EXECUTE querystring LOOP
> RETURN QUERY SELECT reportid, surname, city, state, postcode, telephone, species, breed, status, dist FROM
data_viewWHERE reportid = repid;
> END LOOP;
> RETURN;
> }}}
>
> cheers
>
> Ben
>
>
--
Adrian Klaver
adrian.klaver@gmail.com
