PostgreSQL: PL/pgSQL : Boolean function that tests if a given product exists or not in a table.
Posted on 2007-10-01
The following is a PL/pgSQL boolean function that checks if a product can be found with a given department id, so that the department_id can be deleted altogether - it is used to avoid orphaning any product.
CREATE OR REPLACE FUNCTION test_product_of_department_id_exists(department_id integer) RETURNS boolean AS
This function tests if a product can be found with a given department id.
It is utilized by the frontend to check if a whole department can be deleted without
orphaning a whole bunch of products.
Clearly, orphaning many products in one foul swoop is disastrous!
department_id_var := COUNT(*) FROM stock WHERE department_id = $1 ;
--SELECT INTO department_id_var FROM count(department_id) FROM stock WHERE department_id = $1;
raise notice 'got: %', department_id_var;
IF (department_id_var > 0) THEN
RETURN ( TRUE );
RETURN ( FALSE );
LANGUAGE 'plpgsql' VOLATILE;
It's as if "count(department_id) FROM stock WHERE department_id = $1;" doesn't have the WHERE clause at all - the value of department_id_var, which gets its value assigned from that count query, is just the value of the total number of rows, not rows where the department id is equal to the first argument of the function.
If anyone could offer an insight into what I might be doing wrong, it would be much appreciated!
Thanks in advance,