PostgreSQL triggers and exceptions

Question

I'm trying to get my first ever trigger and function to work, but how I throw exceptions and return data right way?

PostgreSQL 8.4.1

CREATE TABLE "SHIFTS" (
    id integer NOT NULL, -- SERIAL
    added timestamp without time zone DEFAULT now() NOT NULL,
    starts timestamp without time zone NOT NULL,
    ends timestamp without time zone NOT NULL,
    employee_id integer,
    modified timestamp without time zone,
    status integer DEFAULT 1 NOT NULL,
    billid integer,
    CONSTRAINT "SHIFTS_check" CHECK ((starts < ends))
);


-- Check if given shift time overlaps with existing data
CREATE OR REPLACE FUNCTION 
  shift_overlaps (integer, timestamp, timestamp)
RETURNS 
  boolean AS $$
DECLARE
  _employeeid ALIAS FOR $1;
  _start      ALIAS FOR $2;
  _end        ALIAS FOR $3;
BEGIN
  SELECT 
    COUNT(id) AS c
  FROM 
    "SHIFTS" 
  WHERE 
    employee_id = _employeeid AND 
    status = 1 AND
    (
      (starts BETWEEN _start AND _end)
      OR 
      (ends BETWEEN _start AND _end)
    )
  ;

  -- Return boolean
  RETURN (c > 0);
END;
$$
LANGUAGE 
  plpgsql
;


CREATE OR REPLACE FUNCTION
  check_shift()
RETURNS trigger AS '
BEGIN

  -- Bill ID is set, do not allow update
  IF tg_op = "UPDATE" THEN
    IF old.billid IS NOT NULL THEN
      RAISE EXCEPTION "Shift is locked"
    END IF;
  END IF;

  -- Check for overlap
  IF tg_op = "INSERT" THEN
    IF new.employee_id IS NOT NULL THEN
      IF shift_overlaps(new.employee_id, new.starts, new.ends) THEN
        RAISE EXCEPTION "Given time overlaps with shifts"
      END IF;
    END IF;
  END IF;

  -- Check for overlap
  IF tg_op = "UPDATE" THEN
    IF (new.employee_id IS NOT NULL) AND (new.status = 1) THEN
      IF shift_overlaps(new.employee_id, new.starts, new.ends) THEN
        RAISE EXCEPTION "Given time overlaps with shifts"
      END IF;
    END IF;
  END IF;

  RETURN new;
END
'
LANGUAGE
  plpgsql
;


-- Shift checker trigger
CREATE TRIGGER
  check_shifts
BEFORE
  INSERT OR UPDATE
ON 
  "SHIFTS"  
FOR EACH ROW EXECUTE PROCEDURE
  check_shift()
;

shift_overlaps():

SQL error: ERROR: query has no destination for result data

check_shift():

SQL error: ERROR: unrecognized exception condition "Shift is locked"

Answer 1

You've got an error here:

SELECT 
    COUNT(id) AS c
  FROM 
    "SHIFTS" 
  WHERE 
    employee_id = _employeeid AND 
    status = 1 AND
    (
      (starts BETWEEN _start AND _end)
      OR 
      (ends BETWEEN _start AND _end)
    )
  ;

Such a select in a plpgsql procedure has to be SELECT INTO... like this:

DECLARE
 c INTEGER;
BEGIN
  SELECT 
    COUNT(id)
  INTO c
  FROM 
    "SHIFTS" 
  WHERE 
    employee_id = _employeeid AND 
    status = 1 AND
    (
      (starts BETWEEN _start AND _end)
      OR 
      (ends BETWEEN _start AND _end)
    )
  ;

  RETURN (c > 0);

END;

And here you've got to have the semicolon at the end of the line:

enter code here`RAISE EXCEPTION "Shift is locked";

Answer 2

Not sure what you're trying to find out. You're managing to raise your own exceptions, so that's good. I would expect that any error handling would be in the code that evokes this method.

If you want to do something inside the procedure, you need an EXCEPTION section:

[ <> ] [ DECLARE declarations ] BEGIN statements EXCEPTION WHEN condition [ OR condition ... ] THEN handler_statements [ WHEN condition [ OR condition ... ] THEN handler_statements ... ] END;

But generally I would expect you'd handle it in the calling code.

Answer 3

You have to use SELECT INTO to get a value returned by a query

DECLARE
  [...]
  c boolean;
SELECT 
    COUNT(id) INTO c
  FROM 
    "SHIFTS" 
  WHERE 
  [...]