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 :
{{{
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;
}}}
> 2) Why are the integers at the end of the query quoted?
I have quote_literal(speciesid) etc, even thought it is an int parameter to the query. I realise it isn't needed, but it was working on one. FWIW, I have tried it without all of the quotes (manually removed), but it doesn't make any difference to the result.
cheers
Ben
--
Ben Madin
t : +61 8 6102 5535
m : +61 448 887 220
e : ben@xxxxxxxxxxxxx
AusVet Animal Health Services
P.O. Box 5467
Broome WA 6725
Australia
AusVet's website: http://www.ausvet.com.au
This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this email are the opinion of the writer only and are not endorsed by AusVet Animal Health Services unless expressly stated otherwise. Although AusVet uses virus scanning software we do not accept liability for viruses or similar in any attachments. Thanks for reading.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
