kputnam / piggly

PL/pgSQL stored procedure code coverage tool
Other
69 stars 14 forks source link

another parse issue "RAISE {cr} USING" #40

Closed born-in-brooklyn closed 6 years ago

born-in-brooklyn commented 6 years ago

The error appears to be related to this:

raise '{some string}'
using errorcode = {some number}

Here is the unparsable code:

CREATE OR REPLACE FUNCTION bar.foo_start_sp(
    OUT po_out integer,
    OUT po_status text)
  RETURNS record AS
$BODY$
DECLARE
  v_cnt     INTEGER;
  v_state   TEXT;
  v_message TEXT;
BEGIN
  RAISE NOTICE 'Started foo_START_SP';

  LOCK TABLE foo_lock IN ACCESS EXCLUSIVE MODE NOWAIT;

  SELECT count(*)
  INTO v_cnt
  FROM foo_lock l;
  IF v_cnt > 0
  THEN
    RAISE 'Concurrent execution'
    USING ERRCODE = 23054;
  END IF;

  INSERT INTO bar.foo_lock (crtn_ts, updt_ts, stage_nm)
  VALUES (current_timestamp, current_timestamp, 'boo boo processing started');

  UPDATE bar.event_trggr
  SET prcsd_st = 0
  WHERE prcsd_st IS NULL;

  po_out := 1;
  RAISE NOTICE 'Completed foo_START_SP';
  EXCEPTION
  WHEN SQLSTATE '23054'
    THEN
      po_status := 'Exception: Concurrent processing going';
      po_out := -1;
      RAISE WARNING 'Exception: Concurrent processing going';
  WHEN OTHERS
    THEN
      GET STACKED DIAGNOSTICS v_message = MESSAGE_TEXT,
      v_state = RETURNED_SQLSTATE;
      RAISE WARNING 'Exception - % -ERROR- %', v_state, v_message;
      po_out := -2;
      po_status := v_message;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Here's the error:

Compiling riskstg.foo_start_sp
            ****
            Error compiling procedure bar.foo_start_sp
            Source: C:/Users/K26962/RAW/comp-workspace/src/test/resources/sql/pigg    ly/piggly/cache/Dumper/8f65cd90164efdc6ff80a6a102acac8a.plpgsql
            Exception Message:
            Expected one of /*, --, exception, warning, notice, info, log, debug a    t line 16, column 11 (byte 279) after declare
  v_cnt     integer;
  v_state   text;
  v_message text;
begin
  raise notice 'started foo_start_sp';

  lock table foo_lock in access exclusive mode nowait;

  select count(*)
  into v_cnt
  from foo_lock l;
  if v_cnt > 0
  then
    raise
            ****
kputnam commented 6 years ago

Thanks, this should be fixed in the latest release (v2.2.4)