Postgres Pro
Downloads
Home   >   mailing lists

Thread: parser: parse error at or near "$1"

parser: parse error at or near "$1"

From
"Sugandha Shah"
Date:
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
 
GO
 
 
Regards,
-Sugandha

Re: parser: parse error at or near "$1"

From
Oliver Elphick
Date:
On Wed, 2002-08-14 at 13:26, Sugandha Shah wrote:
> 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;
...
>         insert into status_log ( log_id, computer_id,schedule_id,status,

schedule_id in the insert is a column name; the alias subsitutes $1 at
that point.  You had better choose variable names that don't conflict
with anything else.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
========================================   "Let us therefore come boldly unto the throne of grace,     that we may
obtainmercy, and find grace to help in      time of need."           Hebrews 4:16

Re: parser: parse error at or near "$1"

From
Masaru Sugawara
Date:
On Wed, 14 Aug 2002 17:56:16 +0530
"Sugandha Shah" <Sugandhas@cybage.com> wrote:


>           -- 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 ;

  PostgreSQL doesn't have a TOP keyword. You'll need to use a LIMIT keyword.    See docs.
http://www.postgresql.org/idocs/index.php?sql-select.html



Regards,
Masaru Sugawara