Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 9C4A4632D8D
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 10 Sep 2009 10:55:18 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 42981-05 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 10 Sep 2009 13:55:10 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-bw0-f206.google.com (mail-bw0-f206.google.com
	[209.85.218.206])
	by mail.postgresql.org (Postfix) with ESMTP id 00ED863281E
	for <pgsql-sql@postgresql.org>; Thu, 10 Sep 2009 10:55:09 -0300 (ADT)
Received: by bwz2 with SMTP id 2so101241bwz.19
	for <pgsql-sql@postgresql.org>; Thu, 10 Sep 2009 06:55:07 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:from:date:message-id:subject:to:cc:content-type;
	bh=pWuLpJva1NBoAS8UkfP0XFihPm3Z4JPrzRsJ2too0pY=;
	b=bK5sdptf7ozES3Z49NE2eQ8wCcHVa4qNBz0I3N68GtTiOi43SLW3YAM+NrVieeIZ3r
	P73SXxKRxqsKrHOw4wGigDDfpFStscw77LWUnGA2P3JinYFbd0cPzlf8irsBXTSwVbYQ
	Ms8DMr3FWiWKDekdk/BH2UCYLDtYvs6/+EVc8=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:from:date:message-id:subject:to
	:cc:content-type;
	b=rtp4n4LU0df+gFsTP35Pzfq98kyoefRUxF+HcWwsp96HhnL92hRl8QCwHAs3VyhV/e
	FPv5Bd1bsDudc4lcrMtr2fCNzxrMzHD76q10R9MBhDZia0cyuV22CMCn3gs905BNleWa
	dFgBmgl8hLTIaJjzVqCfRE4UAFJhSJW8pdLXM=
MIME-Version: 1.0
Received: by 10.204.136.217 with SMTP id s25mr964377bkt.149.1252590907242;
	Thu, 10 Sep 2009 06:55:07 -0700 (PDT)
In-Reply-To: <4AA8EFAB.8070509@megafon.hr>
References: <4AA8EFAB.8070509@megafon.hr>
From: Marcin Krawczyk <jankes.mk@gmail.com>
Date: Thu, 10 Sep 2009 15:54:47 +0200
Message-ID: <95f6bf9b0909100654p3b5e31b3nb6174b3482eb51b3@mail.gmail.com>
Subject: Re: Trapping 'invalid input syntax for integer'
To: Mario Splivalo <mario.splivalo@megafon.hr>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Content-Type: multipart/alternative; boundary=0015175cd8d213589c0473398c73
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.109 tagged_above=-10 required=5
	tests=BAYES_05=-1.11, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200909/41
X-Sequence-Number: 33106

--0015175cd8d213589c0473398c73
Content-Type: text/plain; charset=ISO-8859-1

Hi, I believe you're looking for invalid_text_representation.
EXCEPTION WHEN invalid_text_representation THEN


regards
mk


2009/9/10 Mario Splivalo <mario.splivalo@megafon.hr>

> Is there a way to trap this error in plpgsql code?
>
> I have a function that accepts integer and character varying. Inside
> that function I need to cast that varchar to integer. Of course,
> sometimes that is not possible.
> When I run function like that, I get this errror:
>
> fidel=# select * from get_account_info_by_tan(1, 'mario');
> ERROR:  invalid input syntax for integer: "mario"
> CONTEXT:  SQL statement "SELECT  user_id FROM user_tans WHERE user_tan =
>  $1 ::bigint"
> PL/pgSQL function "get_account_info_by_tan" line 8 at assignment
>
> Now, I know I could change the SELECT so it looks like:
>
> SELECT user_id FROM user_tans WHERE user_tan::varchar = $1
>
> But, is there a way to trap above error usin EXCEPTION WHEN keyword in
> plpgsql?
>
>        Mike
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

--0015175cd8d213589c0473398c73
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi, I believe you&#39;re looking for=A0invalid_text_representation.<div><br=
></div><div>EXCEPTION WHEN=A0invalid_text_representation THEN<br><div><br><=
/div><div><br clear=3D"all">regards<br>mk<br>
<br><br><div class=3D"gmail_quote">2009/9/10 Mario Splivalo <span dir=3D"lt=
r">&lt;<a href=3D"mailto:mario.splivalo@megafon.hr">mario.splivalo@megafon.=
hr</a>&gt;</span><br><blockquote class=3D"gmail_quote" style=3D"margin:0 0 =
0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">

Is there a way to trap this error in plpgsql code?<br>
<br>
I have a function that accepts integer and character varying. Inside<br>
that function I need to cast that varchar to integer. Of course,<br>
sometimes that is not possible.<br>
When I run function like that, I get this errror:<br>
<br>
fidel=3D# select * from get_account_info_by_tan(1, &#39;mario&#39;);<br>
ERROR: =A0invalid input syntax for integer: &quot;mario&quot;<br>
CONTEXT: =A0SQL statement &quot;SELECT =A0user_id FROM user_tans WHERE user=
_tan =3D<br>
=A0$1 ::bigint&quot;<br>
PL/pgSQL function &quot;get_account_info_by_tan&quot; line 8 at assignment<=
br>
<br>
Now, I know I could change the SELECT so it looks like:<br>
<br>
SELECT user_id FROM user_tans WHERE user_tan::varchar =3D $1<br>
<br>
But, is there a way to trap above error usin EXCEPTION WHEN keyword in<br>
plpgsql?<br>
<br>
 =A0 =A0 =A0 =A0Mike<br>
<font color=3D"#888888"><br>
--<br>
Sent via pgsql-sql mailing list (<a href=3D"mailto:pgsql-sql@postgresql.org=
">pgsql-sql@postgresql.org</a>)<br>
To make changes to your subscription:<br>
<a href=3D"http://www.postgresql.org/mailpref/pgsql-sql" target=3D"_blank">=
http://www.postgresql.org/mailpref/pgsql-sql</a><br>
</font></blockquote></div><br></div></div>

--0015175cd8d213589c0473398c73--