Re: getting y/n or 1/0 from bool fields - Mailing list pgsql-general

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