Mailbox-Line: From sschildknecht@aurora-linux.com  Thu Jul  4 13:41:39 2002
Subject: Boolean to int
From: Stephane Schildknecht <sschildknecht@aurora-linux.com>
To: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: quoted-printable
Date: 04 Jul 2002 19:26:35 +0200
Message-Id: <1025803595.20819.239.camel@fukse>
Mime-Version: 1.0

Hi,=20

        I'd like to get 1 or 0 from a select on a boolean field.=20

How could I do ?

I tried the following rule, which I can't insert :

CREATE FUNCTION bool_to_int( boolean ) RETURNS integer AS '
        DECLARE
                my_bool ALIAS FOR $1 ;
        BEGIN
                -- Retourne 1 si true, 0 si false
                IF my_bool IS FALSE THEN=20
                        RETURN 0 ;
                ELSIF my_bool IS TRUE THEN
                                RETURN 1 ;
                ELSE RETURN -1 ;
                END IF ;
        END ;
' LANGUAGE 'plpgsql';

CREATE RULE boolean_return AS ON SELECT TO DOCUMENT DO INSTEAD
        SELECT=20
        document_id,
        workflow_id,
        type_document_id,
        image_id,
        theme_id,
        document_version,
        document_surtitre,
        document_titre,
        document_chapeau,
        document_synthese,
        document_corps,
        document_pdf,
        document_date_creation,
        document_mot_clef,
        (bool_to_int(document_online)) as document_online,
        bool_to_int(document_valid) as document_valid FROM document;


psql:cnambo_proc_stock.sql:69: ERROR:  select rule's target entry 15
 has different type from attribute document_online

Who could help me ?

S@S

--=20
 _St=E9phane SCHILDKNECHT___________________________________
| AurorA-SAS 69-71, Av. Pierre Grenier 92100 BOULOGNE     |
| Tel : 01.58.17.03.20 Fax : 01.58.17.03.21               |=20
| mailto:sschildknecht@aurora-linux.com - ICQ : 142504394 |
| "Free Markets have taught that innovation is best when  |=20
|  ideas flow freely." Adam Smith                         |
|_________________________________________________________|