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 :
{{{
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 : b...@ausvet.com.au
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
