Re: ERROR: invalid input syntax for integer: "" - Mailing list pgsql-general

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 :
>>=20
>> {{{
>> SELECT rep.id, res8.reportid, =
round(st_distance_sphere('0101000020BB1000008716D9CEF7A36240643BDF4F8DA741=
C0', post.the_point::geometry)/1000) as dist
>> FROM reports rep
>> LEFT JOIN users u ON rep.link =3D u.id
>> LEFT JOIN postcodes post ON u.postcode::integer =3D post.postcode
>> LEFT JOIN species spe ON rep.species::text like spe.speccode::text =
AND spe.synonym =3D 0
>> LEFT JOIN results res8 ON res8.reportid =3D rep.id AND =
res8.resulttypeid =3D 108 AND res8.del =3D false
>> LEFT JOIN resultlookup rlu8 ON rlu8.resulttypesid =3D 108 AND rlu8.id =
=3D res8.resultvalue::int
>> WHERE rep.del IS false AND rep.projectid =3D 51
>> AND round(st_distance_sphere( =
'0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0', =
post.the_point)/1000) < '150' AND spe.id =3D '9465' AND rlu8.id =3D =
'935';
>> }}}
>>=20
>=20
>=20
> Follow up questions:
>=20
> 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 =3D =
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


--=20

Ben Madin

t : +61 8 6102 5535
m : +61 448 887 220
e : ben@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.