On 02/05/2013 09:01 PM, Ben Madin wrote:
Thanks Adrian,
On 2013-02-06, at 12:52 , Adrian Klaver <adrian.kla...@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' 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.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
