Thread: getting y/n or 1/0 from bool fields

Hello all,

Some of our development software is not really "PostgreSQL aware" in
that it expects bool values to be y/n (ala MS Access) or 1/0.  However
it seems that Postgres returns t/f.

Does anyone know a way to get Postgres to return one of the above
(preferably, 1/0) for boolean values?

Ideally, it would be somewhat of a transparent feature, so that a
"SELECT * FROM table;" would have all bool values in the format above.

Any suggestions greatly apreciated,

Matthew Nuzum

On Mon, 2002-09-02 at 16:01, Matthew Nuzum wrote:

> Some of our development software is not really "PostgreSQL aware" in
> that it expects bool values to be y/n (ala MS Access) or 1/0.  However
> it seems that Postgres returns t/f.
>
> Does anyone know a way to get Postgres to return one of the above
> (preferably, 1/0) for boolean values?
>
> Ideally, it would be somewhat of a transparent feature, so that a
> "SELECT * FROM table;" would have all bool values in the format above.
>
> Any suggestions greatly apreciated,

My JSP applications don't care. The column is y/n type and y = t and n =
f. Where the conversion takes place I do not know?

Cheers

Tony Grant

--
RedHat Linux on Sony Vaio C1XD/S
http://www.animaproductions.com/linux2.html
Macromedia UltraDev with PostgreSQL
http://www.animaproductions.com/ultra.html

Matthew Nuzum sez:
} Hello all,
}
} Some of our development software is not really "PostgreSQL aware" in
} that it expects bool values to be y/n (ala MS Access) or 1/0.  However
} it seems that Postgres returns t/f.
}
} Does anyone know a way to get Postgres to return one of the above
} (preferably, 1/0) for boolean values?
}
} Ideally, it would be somewhat of a transparent feature, so that a
} "SELECT * FROM table;" would have all bool values in the format above.

Suppose you have a table:

CREATE TABLE Foo (
    id SERIAL not null,
    istrue boolean not null,
    primary key (id)
);

...but you want y or n instead of t or f...

CREATE VIEW FooView AS (
    SELECT id, (CASE WHEN istrue THEN 'y' ELSE 'n')
    FROM Foo
);

If you feel like encapsulating it, you can do the following:

CREATE FUNCTION yesno(boolean) RETURNS char(1)
AS 'SELECT (CASE WHEN $1 THEN \'y\' ELSE \'n\')'
LANGUAGE SQL;

} Any suggestions greatly apreciated,
} Matthew Nuzum
--Greg