Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 52648632EE4 for ; Thu, 10 Sep 2009 09:23:27 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 86039-01 for ; Thu, 10 Sep 2009 12:23:08 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from megafon.hr (megafon.hr [213.202.104.5]) by mail.postgresql.org (Postfix) with ESMTP id CD6EE6321F2 for ; Thu, 10 Sep 2009 09:23:15 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by megafon.hr (Postfix) with ESMTP id A48CD250128 for ; Thu, 10 Sep 2009 14:23:12 +0200 (CEST) X-Virus-Scanned: ClamAV at megafon.hr Received: from megafon.hr ([127.0.0.1]) by localhost (localhost.localdomain [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id dNiPYE8JuiSv for ; Thu, 10 Sep 2009 14:23:07 +0200 (CEST) Received: from [192.168.1.94] (localhost.localdomain [127.0.0.1]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) (Authenticated sender: mike) by megafon.hr (Postfix) with ESMTPSA id 71A12250124 for ; Thu, 10 Sep 2009 14:23:07 +0200 (CEST) Message-ID: <4AA8EFAB.8070509@megafon.hr> Date: Thu, 10 Sep 2009 14:23:07 +0200 From: Mario Splivalo User-Agent: Thunderbird 2.0.0.23 (X11/20090817) MIME-Version: 1.0 To: "pgsql-sql@postgresql.org" Subject: Trapping 'invalid input syntax for integer' Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200909/40 X-Sequence-Number: 33105 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