[SQL] parser: parse error at or near "$1"

Sugandha Shah Wed, 14 Aug 2002 05:01:22 -0700

Hi ,

I'm getting this error :

Error occurred while executing PL/pgSQL function ins_schedule_status
line 42 at SQL statement
parser: parse error at or near "$1"

Unable to locate the cause. Please any hint or clue will be of great help.

Below is the function

CREATE FUNCTION ins_schedule_status(int4,time without time zone,varchar(256),int2,int2,int2) RETURNS int4 AS '
DECLARE
     schedule_id ALIAS FOR $1;
     var_current_time ALIAS FOR $2;
     status ALIAS FOR $3;
     status_code ALIAS FOR $4;
     module ALIAS FOR $5;
     var_level ALIAS FOR $6 ;
     last_status    varchar(256) ;
     last_code      int2;
     level_value int2 ;
BEGIN
      IF ( var_level != 0 )THEN
             --select level_value =var_level;
             level_value :=var_level;
      ELSE

          IF ( status_code = 0 or status_code < 0 )THEN
            --select level_value = 1 ;
             level_value := 1 ;
          ELSE
            --select level_value = 3 ;
            level_value := 3 ;

         END IF;
      END IF;

           -- dont report server control values, just positive values
       IF ( status_code < 0 ) THEN
         --select status_code = null;
         --status_code := null;

          -- check for the exact same status string reported
          -- previously and skip it if found

          select into last_status top 1 status, last_code status_code from status_log where schedule_id =schedule_id order by complete_time desc ;
          if ( last_status = status and ( ( last_code is null and status_code is null ) or last_code = status_code ) ) THEN
                       return ;
          END IF;

       END IF;

        insert into status_log ( log_id, computer_id,schedule_id,status,
         status_code,
         module,
         level,
         user_id,
         schedule_time,
         complete_time )
   select 1,
      computer_id,
      schedule_id,
      status,
      status_code,
      module,
      level_value,
      user_id,
      start_time,
      var_current_time
      from
         event_schedule
      where
         schedule_id =schedule_id;

return status_code;

END;'
LANGUAGE 'plpgsql' ;

Original MS _SQL stored procedure

create procedure ins_schedule_status
   @schedule_id     int,
   @current_time    datetime,
   @status          varchar(254),
   @status_code     smallint,
   @module          smallint,
   @level           smallint = 0
as
   declare @level_value smallint
   if ( @level != 0 )
      select @level_value = @level
   else
   begin
      if ( @status_code = 0 or @status_code < 0 )
         select @level_value = 1
      else
         select @level_value = 3
   end

   -- don't report server control values, just positive values
   if ( @status_code < 0 )
      select @status_code = null

   -- check for the exact same status string reported
   --    previously and skip it if found
   declare @last_status    varchar(256)
   declare @last_code      smallint
   select top 1 @last_status = status, @last_code = status_code from status_log
      where schedule_id = @schedule_id order by complete_time desc
   if ( @last_status = @status and ( ( @last_code is null and @status_code is null ) or @last_code = @status_code ) )
      return

declare @tran int
set @tran = @@trancount

if (@tran = 0)
begin transaction

   insert into status_log
      ( computer_id,
         schedule_id,
         status,
         status_code,
         [module],
         [level],
         [user_id],
         schedule_time,
         complete_time )
   select
      computer_id,
      schedule_id,
      @status,
      @status_code,
      @module,
      @level_value,
      user_id,
      start_time,
      @current_time
      from
         event_schedule
      where
         schedule_id = @schedule_id

   if (@@error != 0)
   begin
      if (@tran = 0)
         rollback transaction
   end

if (@tran = 0)
commit transaction

Regards,

-Sugandha

[SQL] parser: parse error at or near "$1"

Reply via email to