Re: Boolean to int

Поиск
Список
Период
Сортировка
Искать

Re: Boolean to int

От:
Arjen van der Meijden|Moderator P&W / Serverbeheer <acm@tweakers.net>
Дата:
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 doesn’t 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                         |
> |_________________________________________________________|
> 
>

Boolean to int

От:
Stephane Schildknecht <sschildknecht@aurora-linux.com>
Дата:
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                        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        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                         |
|_________________________________________________________|

Re: Boolean to int

От:
Stephane Schildknecht <sschildknecht@aurora-linux.com>
Дата:
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                         |
|_________________________________________________________|

Re: Boolean to int

От:
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Дата:

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.

Re: Boolean to int

От:
Arjen van der Meijden <acm@tweakers.net>
Дата:
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
> 
>
FAQ