On 02/05/2013 09:01 PM, Ben Madin wrote:
Thanks Adrian,
On 2013-02-06, at 12:52 , Adrian Klaver <adrian.klaver@xxxxxxxxx> 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' AND spe.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 corresponding rows. This function is working on the dev machine. The query I have appended is produced in the function as below. When I throw the query as above at the psql command line, it works on the dev machine. (but not on the production box). 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_view WHERE reportid = repid;
END LOOP;
RETURN;
}}}
cheers
Ben
--
Adrian Klaver
adrian.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
