Thread: problem with PL/pgSQL

problem with PL/pgSQL

From

"Tiberiu Craciun"

Date:

24 June 1999, 09:25:06

I have a table and a function defined like that :

create table trafic (
    statie text,
    traf_in int8 default 0,
    traf_out int8 default 0
);

create function addtrafic (text, int8, int2) returns int2 as '
    declare
         statia alias for $1 ;
         traficul alias for $2 ;
         directia alias for $3 ;
    begin
         select * from trafic where statie= $1;
         if not found then
                 insert into trafic (statie) values( $1 );
         endif;
         if $3 < 1 then
                 update trafic set traf_in=traf_in+ $2 where statie= $1;
         else
                 update trafic set traf_out=traf_out+ $2 where statie= $1;
         endif;
         return $3 ;
    end;
' language 'plpgsql';

when I run this SQL :

select addtrafic('station1',10,0) as test;

I got an error like that :

NOTICE: plpgsql: ERROR during compile of addtrafic near line 20

ERROR: parse error at or near ";"

and i don't know what is wrong, can someone help me ?

Re: [SQL] problem with PL/pgSQL

From

Eric BASIER

Date:

24 June 1999, 10:53:11

I insert my response in your text
Tiberiu Craciun wrote:

>  I have a table and a function defined like that : create table trafic
> (
>     statie text,
>     traf_in int8 default 0,
>     traf_out int8 default 0
> ); create function addtrafic (text, int8, int2) returns int2 as '
>     declare
>          statia alias for $1 ;
>          traficul alias for $2 ;
>          directia alias for $3 ;
>     begin
>          select * from trafic where statie= $1;
>          if not found then
>                  insert into trafic (statie) values( $1 );
                          there is an error here  ^
the good sentence is : insert into trafic (statie) values ($1) ;



>          endif;
>          if $3 < 1 then
>                  update trafic set traf_in=traf_in+ $2 where statie=
> $1;
>          else
>                  update trafic set traf_out=traf_out+ $2 where statie=
> $1;
>          endif;
>          return $3 ;
>     end;
> ' language 'plpgsql';  when I run this SQL : select
> addtrafic('station1',10,0) as test; I got an error like that : NOTICE:
> plpgsql: ERROR during compile of addtrafic near line 20ERROR: parse
> error at or near ";" and i don't know what is wrong, can someone help
> me ?



--
Basier Eric  e-mail : basier@ipgp.jussieu.fr
http://geoscope.ipgp.jussieu.fr
IPGP Observatoires Sismologie-Volcanologie
4,Place Jussieu 75005 Paris Tour 24-14 4eme Etage Tel 01 44 27 38 96

Re: [SQL] problem with PL/pgSQL

From

José Soares

Date:

24 June 1999, 12:08:51

Your function have some bugs;   1) The parser doesn't understand keywords like ENDIF  but END  IF
(two words)   2) You must to use keyword INTO with the select command
try this one instead:

create function addtrafic (text, int8, int2) returns int2 as '   declare        boo text;        statia alias for $1 ;
     traficul alias for $2 ;        directia alias for $3 ;   begin        select statie from trafic into boo where
statie=$1;        if not found then                insert into trafic (statie) values( $1 );        end if;        if
$3< 1 then                update trafic set traf_in=traf_in+ $2 where statie= $1;
 
        else                update trafic set traf_out=traf_out+ $2 where statie=
$1;        end if;        return $3 ;   end;
' language 'plpgsql';


Tiberiu Craciun ha scritto:

>    Part 1.1    Type: Plain Text (text/plain)
>            Encoding: quoted-printable

--
______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'