incorrect syntax for 'plpgsql' function to test boolean values

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

incorrect syntax for 'plpgsql' function to test boolean values

Ferindo Middleton Jr
I'm trying to write a function and trigger to validate that user data
entry for boolean values makes sense before being inserted or updated
into my database. I have the following trigger:

CREATE TRIGGER trigger_registration_and_attendance
BEFORE INSERT OR UPDATE
ON registration_and_attendance
FOR EACH ROW
EXECUTE PROCEDURE trigger_insert_update_registration_and_attendance();

Here  is the problem: Below is the first part of the function called
from the above trigger

1: CREATE FUNCTION trigger_insert_update_registration_and_attendance()
2: RETURNS opaque
3: AS 'DECLARE
4:    schedules_record RECORD;
5:    BEGIN
6:
7:    /* To ensure the integrity of boolean variables this database
stores to identify the
8:      status of a registration */
9:     IF ((new.enrolled == true) && (new.waitlisted == true))
10:      THEN RAISE EXCEPTION ''Participant cannot be Enrolled AND
Waitlisted at the same time.'';
11:      END IF;
12:      IF ((new.enrolled == true) && (new.cancelled == true))
13:      THEN RAISE EXCEPTION ''Participant cannot be Enrolled and
Cancelled at the same time.'';
14:      END IF;

I get he following error message when I try inserting a record:

ERROR:  operator does not exist: boolean == boolean
HINT:  No operator matches the given name and argument type(s). You may
need to add explicit type casts.
CONTEXT:  SQL statement "SELECT  (( $1  == true) && ( $2  == true))"
PL/pgSQL function "trigger_insert_update_registration_and_attendance"
line 13 at if

What is wrong with my syntax above?

Ferindo

--
Ferindo Middleton
Chief Architect
Sleekcollar.com


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: incorrect syntax for 'plpgsql' function to test boolean values

John DeSoi

On Aug 6, 2005, at 10:52 PM, Ferindo Middleton Jr wrote:

> ERROR:  operator does not exist: boolean == boolean
> HINT:  No operator matches the given name and argument type(s). You  
> may need to add explicit type casts.
> CONTEXT:  SQL statement "SELECT  (( $1  == true) && ( $2  == true))"
> PL/pgSQL function  
> "trigger_insert_update_registration_and_attendance" line 13 at if
>
> What is wrong with my syntax above?
>

Too much C programming :). You just want a single equal sign.

select true = true;
?column?
----------
t
(1 row)




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: incorrect syntax for 'plpgsql' function to test boolean

Ferindo Middleton Jr
Yeah, I guess so..... I just didn't want the compiler to think I was
trying to assign the value..... And I also figured out that instead of
the &&, I needed to just say AND.... Thanks.

Ferindo

John DeSoi wrote:

>
> On Aug 6, 2005, at 10:52 PM, Ferindo Middleton Jr wrote:
>
>> ERROR:  operator does not exist: boolean == boolean
>> HINT:  No operator matches the given name and argument type(s). You  
>> may need to add explicit type casts.
>> CONTEXT:  SQL statement "SELECT  (( $1  == true) && ( $2  == true))"
>> PL/pgSQL function  
>> "trigger_insert_update_registration_and_attendance" line 13 at if
>>
>> What is wrong with my syntax above?
>>
>
> Too much C programming :). You just want a single equal sign.
>
> select true = true;
> ?column?
> ----------
> t
> (1 row)
>
>
>
>
> John DeSoi, Ph.D.
> http://pgedit.com/
> Power Tools for PostgreSQL
>
>

--
Ferindo Middleton
Chief Architect
Sleekcollar.com


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match