pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBoolean to int
5+ messages / 4 participants
[nested] [flat]
* Boolean to int
@ 2002-07-04 17:26 Stephane Schildknecht <sschildknecht@aurora-linux.com>
0 siblings, 2 replies; 5+ messages in thread
From: Stephane Schildknecht @ 2002-07-04 17:26 UTC (permalink / raw)
To: pgsql-sql
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 15
has 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 |
|_________________________________________________________|
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Boolean to int
@ 2002-07-04 18:45 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Stephane Schildknecht <sschildknecht@aurora-linux.com>
1 sibling, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2002-07-04 18:45 UTC (permalink / raw)
To: Stephane Schildknecht <sschildknecht@aurora-linux.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Boolean to int
@ 2002-07-04 21:32 Arjen van der Meijden <acm@tweakers.net>
parent: Stephane Schildknecht <sschildknecht@aurora-linux.com>
1 sibling, 1 reply; 5+ messages in thread
From: Arjen van der Meijden @ 2002-07-04 21:32 UTC (permalink / raw)
To: pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Boolean to int
@ 2002-07-05 13:38 Stephane Schildknecht <sschildknecht@aurora-linux.com>
parent: Arjen van der Meijden <acm@tweakers.net>
0 siblings, 1 reply; 5+ messages in thread
From: Stephane Schildknecht @ 2002-07-05 13:38 UTC (permalink / raw)
To: Arjen van der Meijden <acm@tweakers.net>; +Cc: pgsql-sql
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 |
|_________________________________________________________|
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Boolean to int
@ 2002-07-05 15:12 Arjen van der Meijden|Moderator P&W / Serverbeheer <acm@tweakers.net>
parent: Stephane Schildknecht <sschildknecht@aurora-linux.com>
0 siblings, 0 replies; 5+ messages in thread
From: Arjen van der Meijden|Moderator P&W / Serverbeheer @ 2002-07-05 15:12 UTC (permalink / raw)
To: Stephane Schildknecht <sschildknecht@aurora-linux.com>; +Cc: pgsql-sql
Perhaps the 'create rule' is in the way, you could try something like:
CASE WHEN document_online THEN 1
ELSE 0
END,
CASE WHEN document_valid 1
ELSE 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 |
> |_________________________________________________________|
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-07-05 15:12 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-04 17:26 Boolean to int Stephane Schildknecht <sschildknecht@aurora-linux.com>
2002-07-04 18:45 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-04 21:32 ` Arjen van der Meijden <acm@tweakers.net>
2002-07-05 13:38 ` Stephane Schildknecht <sschildknecht@aurora-linux.com>
2002-07-05 15:12 ` Arjen van der Meijden|Moderator P&W / Serverbeheer <acm@tweakers.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox