[SQL] Boolean to int

[Stephane Schildknecht]

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:[EMAIL PROTECTED] - ICQ : 142504394 |
| "Free Markets have taught that innovation is best when  | 
|  ideas flow freely." Adam Smith |
|_|




---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] how to use nextval()

[Stephane Schildknecht]

Le ven 05/07/2002 à 14:03, [EMAIL PROTECTED] a écrit :
> Hello there
> 
> I have an idea how to solve my problem on copy a record to a new one in the
> same table. To do so I need the next value for the primary key.
> 
> Using :
> 
> select nextval('pk_auftrag') as nextkey from auftrag;
> 
> I get an error like : Relation pk_auftrag does not exist !
> 
> Thanks for any help ... jr
Try to get the right name of the sequence.
By default, something like auftrag_auftrag_id_seq, if auftrag_id is the
serial primary key.

select nextval('auftrag_auftrag_id_seq') as nextkey;

S@S




---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Boolean to int

[Stephane Schildknecht]

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:[EMAIL PROTECTED] - ICQ : 142504394 |
| "Free Markets have taught that innovation is best when  | 
|  ideas flow freely." Adam Smith |
|_|




---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html