Thread: Boolean to int
Hi,
I'd like to get 1 or 0 from a select on a boolean field.
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
RETURN0 ; 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 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 15has different type from attribute document_online
Who could help me ?
S@S
-- _Stéphane SCHILDKNECHT___________________________________
| AurorA-SAS 69-71, Av. Pierre Grenier 92100 BOULOGNE |
| Tel : 01.58.17.03.20 Fax : 01.58.17.03.21 |
| mailto:sschildknecht@aurora-linux.com - ICQ : 142504394 |
| "Free Markets have taught that innovation is best when |
| ideas flow freely." Adam Smith |
|_________________________________________________________|
On 4 Jul 2002, Stephane Schildknecht wrote: > CREATE RULE boolean_return AS ON SELECT TO DOCUMENT DO INSTEAD > SELECT > 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 You might have better luck defining a view on Document that does it. Select rules are fairly limited except as the apply to making views work.
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 > >
Le jeu 04/07/2002 à 23:32, Arjen van der Meijden a écrit : > 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 I tried that : CREATE RULE boolean_return AS ON SELECT TO DOCUMENT DO INSTEAD SELECT 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, document_online, document_valid, CASE document_online WHEN TRUE THEN 1 WHEN FALSE THEN 0 END, CASE document_valid WHEN TRUE THEN 1 WHEN FALSE THEN 0 END FROM document; And, that doesn't work either... psql:cnambo_proc_stock.sql:76: ERROR: select rule's target list has too many entries > -- _Stéphane SCHILDKNECHT___________________________________ | AurorA-SAS 69-71, Av. Pierre Grenier 92100 BOULOGNE | | Tel : 01.58.17.03.20 Fax : 01.58.17.03.21 | | mailto:sschildknecht@aurora-linux.com - ICQ : 142504394 | | "Free Markets have taught that innovation is best when | | ideas flow freely." Adam Smith | |_________________________________________________________|
Perhaps the 'create rule' is in the way, you could try something like: CASE WHEN document_online THEN 1ELSE 0 END, CASE WHEN document_valid 1ELSE 0 END But if that doesnt work, I'm out of options aswel. You could try the "instead query" separately to test whether the query itself is correct. Goodluck > -----Oorspronkelijk bericht----- > Van: Stephane Schildknecht [mailto:sschildknecht@aurora-linux.com] > Verzonden: vrijdag 5 juli 2002 15:39 > Aan: Arjen van der Meijden > CC: pgsql-sql@postgresql.org > Onderwerp: Re: [SQL] Boolean to int > > > Le jeu 04/07/2002 à 23:32, Arjen van der Meijden a écrit : > > 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 > > I tried that : > > CREATE RULE boolean_return AS ON SELECT TO DOCUMENT DO INSTEAD > SELECT > 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, > document_online, > document_valid, > CASE document_online WHEN TRUE THEN 1 > WHEN FALSE THEN 0 > END, > CASE document_valid WHEN TRUE THEN 1 > WHEN FALSE THEN 0 > END > FROM document; > > And, that doesn't work either... > > psql:cnambo_proc_stock.sql:76: ERROR: select rule's target > list has too many entries > > > > > -- > _Stéphane SCHILDKNECHT___________________________________ > | AurorA-SAS 69-71, Av. Pierre Grenier 92100 BOULOGNE | > | Tel : 01.58.17.03.20 Fax : 01.58.17.03.21 | > | mailto:sschildknecht@aurora-linux.com - ICQ : 142504394 | "Free > |Markets have taught that innovation is best when | > | ideas flow freely." Adam Smith | > |_________________________________________________________| > >