X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id F2C71D88E5
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  1 Sep 2005 18:23:31 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 27077-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  1 Sep 2005 21:23:30 +0000 (GMT)
Received: from nelson.osl.com (216-21-133-120.ip.van.radiant.net
	[216.21.133.120])
	by svr1.postgresql.org (Postfix) with ESMTP id E0266D88D0
	for <pgsql-sql@postgresql.org>; Thu,  1 Sep 2005 18:23:28 -0300 (ADT)
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Subject: Re: [HACKERS] ALTER TABLE ( smallinto -> boolean ) ... 
X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0
Date: Thu, 1 Sep 2005 14:24:59 -0700
Message-ID: <144D12D7DD4EC04F99241498BB4EEDCC08AB03@nelson.osl.com>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [SQL] [HACKERS] ALTER TABLE ( smallinto -> boolean ) ... 
Thread-Index: AcWvOsbfHu+P7t9UR26GX6dGeREL8QAAFsIQ
From: "Owen Jacobson" <ojacobson@osl.com>
To: <pgsql-sql@postgresql.org>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.153 required=5 tests=[AWL=-0.104,
	FORGED_RCVD_HELO=0.05, UPPERCASE_25_50=0.207]
X-Spam-Level: 
X-Archive-Number: 200509/2
X-Sequence-Number: 22549

Marc G. Fournier wrote:

> Moved off of -hackers, since its long gotten out of that realm :)
>=20
> On Thu, 1 Sep 2005, Tom Lane wrote:
>=20
> > "Marc G. Fournier" <scrappy@postgresql.org> writes:
> >> On Mon, 29 Aug 2005, Tom Lane wrote:
> >>> No, because there's no built-in cast from smallint to bool.
> >
> >> 'k, I just took a read through the "CREATE CAST" man page,=20
> and don't think
> >> I can use that for this,
> >
> > Sure you can.  Make a SQL or PLPGSQL function that does the=20
> conversion
> > you want and then create a cast using it.
>=20
> Ah, okay, I just re-read the man page and think I stumbled=20
> upon what I=20
> overlooked the first time ...
>=20
> all I want to do is:
>=20
> CREATE CAST ( 0 AS boolean )
>         WITH FUNCTION <I have to create this>
>           AS ASSIGNMENT;
>=20
> And then each time I try to insert a '0' into a BOOLEAN=20
> field, it will=20
> auto convert that (based on my function) to 'f' ...
>=20
> And I'd need to do a second one for 1 -> 't' ...
>=20
> Am I reading it right this time ... ?

Here's what I'm using:

CREATE OR REPLACE FUNCTION int2bool (INTEGER) RETURNS BOOLEAN AS $$
  SELECT ($1 !=3D 0);
$$ LANGUAGE 'SQL' IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION bool2int (BOOLEAN) RETURNS INTEGER AS $$
  SELECT CASE WHEN $1 THEN 1
              WHEN NOT $1 THEN 0
              ELSE NULL END;
$$ LANGUAGE 'SQL' IMMUTABLE STRICT;

CREATE CAST (INTEGER AS BOOLEAN)
  WITH FUNCTION int2bool (INTEGER)
  AS ASSIGNMENT;

CREATE CAST (BOOLEAN AS INTEGER)
  WITH FUNCTION bool2int (BOOLEAN)
  AS ASSIGNMENT;

done.

Owen