• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5035
  • Last Modified:

PostgreSQL: PL/pgSQL : Boolean function that tests if a given product exists or not in a table.

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
$BODY$
/*
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!
*/
DECLARE
department_id_var    integer;


BEGIN
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 );
ELSE
RETURN ( FALSE );
END IF;

END;$BODY$
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,
Sternocera
0
sternocera
Asked:
sternocera
  • 3
  • 3
1 Solution
 
sternoceraAuthor Commented:
I should add that "SELECT COUNT(*) FROM stock WHERE department_id = 5;" works fine; the value returned is the total number of rows where department_id = 5, at the moment, 1 :

lustre=# SELECT COUNT(*) FROM stock WHERE department_id = 5 ;
 count
-------
     1

However, as mentioned before, :

lustre=# SELECT test_product_of_department_id_exists(5);
NOTICE:  got: 2
 test_product_of_department_id_exists
--------------------------------------
 t
(1 row)

0
 
earth man2Commented:
CREATE OR REPLACE FUNCTION test_product_of_department_id_exists(department_id integer) RETURNS boolean AS $$
DECLARE
department_id_var    integer;
BEGIN
  SELECT count(department_id) INTO department_id_var FROM stock WHERE department_id = $1;
  raise notice 'got: %', department_id_var;
  RETURN department_id_var > 0;
END;
$$ LANGUAGE 'plpgsql' VOLATILE;
0
 
earth man2Commented:
CREATE OR REPLACE FUNCTION test_product_of_department_id_exists( integer ) RETURNS boolean AS $$
BEGIN
  RETURN exists( SELECT 1 FROM stock WHERE department_id = $1);
END;
$$ LANGUAGE 'plpgsql' VOLATILE;


0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sternoceraAuthor Commented:
Apparently, the problem was that there was ambiguity - I wasn't aware that it was now possible to refer to a function argument directly, rather then through $1, etc.

I'll give you the points, for offering a more efficient implementation,

Thanks
0
 
earth man2Commented:
I was implying the possible ambiguity when I removed the argument name.  Usually I use "the" to differentiate between function arguments names and column names ie

CREATE OR REPLACE FUNCTION test_product_of_department_id_exists( the_dept_id integer ) RETURNS boolean AS $$
BEGIN
  RETURN exists( SELECT 1 FROM stock WHERE department_id = the_dept_id );
END;
$$ LANGUAGE 'plpgsql' VOLATILE;

this helps maintain readability of the code.

0
 
sternoceraAuthor Commented:
earthman2,

I'll follow your example and start function arguments with "the_".

Thanks,
Sternocera
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now