Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id A2F39476823 for ; Thu, 4 Jul 2002 17:37:12 -0400 (EDT) Mailbox-Line: From acm@tweakers.net Thu Jul 4 17:37:12 2002 Received: from news.hub.org (news.hub.org [64.49.215.80]) by postgresql.org (Postfix) with ESMTP id A450B476845 for ; Thu, 4 Jul 2002 17:33:04 -0400 (EDT) Received: by news.hub.org (Postfix, from userid 8) id F040E382019; Thu, 4 Jul 2002 17:32:38 -0400 (EDT) From: Arjen van der Meijden X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Boolean to int Date: Thu, 04 Jul 2002 23:32:20 +0200 Organization: Delft University of Technology Lines: 81 Message-ID: References: <1025803595.20819.239.camel@fukse> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.tudelft.nl User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.1a+) Gecko/20020701 X-Accept-Language: en-us, en To: pgsql-sql@postgresql.org X-Spam-Status: No, hits=-0.6 required=5.0 tests=DOUBLE_CAPSWORD,ASCII_FORM_ENTRY version=2.30 X-Spam-Level: X-Archive-Number: 200207/63 X-Sequence-Number: 8442 How about this hint in the postgresql-manual: "Tip: Values of the boolean type cannot be cast directly to other types (e.g., CAST (boolval AS integer) does not work). This can be accomplished using the CASE expression: CASE WHEN boolval THEN 'value if true' ELSE 'value if false' END. See also Section 4.12. " For more information: http://www.postgresql.org/idocs/index.php?datatype-boolean.html and http://www.postgresql.org/idocs/index.php?functions-conditional.html Stephane Schildknecht wrote: > 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 | > |_________________________________________________________| > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > >