CODEWITHSUNDEEP
sqlpostgresql
Jake
Jake

Reputation: 391

What's the error in this PostgreSQL function code at line 17?

PostgreSQL is throwing an unrecognized error at line 17 in pgpAdmin and I have no idea why. I checked all the documentation and I'm sure I have all the statements right.

I'm using the following PostgreSQL version:

PostgreSQL 9.3.4, compiled by Visual C++ build 1600, 64-bit

Here's the code:

Tried commenting out the surrounding offending lines. Double checked all syntax.

CREATE OR REPLACE FUNCTION sp_test_crud (
    op VARCHAR,
    test_id INTEGER, 
    part_id BIGINT, 
    station_id BIGINT,
    pass_val BOOLEAN,
    name_val VARCHAR(50),
    start_datetime timestamp with time zone,
    end_datetime timestamp with time zone
)
RETURNS SETOF public.test
AS $$
DECLARE return_test_id BIGINT;
BEGIN
    IF op = 'SELECT' THEN
        RETURN QUERY (SELECT * FROM public.test WHERE id = test_id);
    ELSIF op = 'DELETE' THEN
        DELETE FROM public.test WHERE id = test_id RETURNING id INTO return_test_id;
        RETURN QUERY SELECT * FROM return_test_id;
    ELSIF (op = 'UPDATE') THEN
        UPDATE public.test SET partid = part_id, stationid = station_id, pass = pass_val, name = name_val, startdatetime = start_datetime, enddatetime = end_datetime
        WHERE testid = test_id RETURNING id INTO return_test_id;
        RETURN QUERY SELECT * FROM return_test_id;
    ELSIF op = 'INSERT' THEN
        INSERT INTO public.test (partid, stationid, name, startdatetime, enddatetime, pass) 
        VALUES (part_id, station_id, name_val, start_datetime, end_datetime, pass_val) RETURNING id INTO return_test_id;
        RETURN QUERY SELECT * FROM return_test_id;
    ELSE
        RAISE ERROR 'Operation ' + op + ' not valid.';
    END IF;

  RETURN;
END;
$$
LANGUAGE 'plpgsql';

It keeps generating this error.

ERROR:  unrecognized exception condition "error"
CONTEXT:  compilation of PL/pgSQL function "sp_test_crud" near line 18
SQL state: 42704

Upvotes: 0

Views: 182

Answers (1)

Frank Schmitt
Frank Schmitt

Reputation: 30785

ERROR is not a pre-defined level for RAISE. According to the PostgreSQL documentation, you can use

DEBUG, LOG, INFO, NOTICE, WARNING, and EXCEPTION

Upvotes: 1

Related Questions