Re: plpgsql function

Hello Marcin,

Once, Tuesday, September 05, 2000, 8:42:59 PM, you wrote:

MM> Hi,
MM> creation of following function works just fine but usage not:):
MM> mtldb=# select mtldb_wykonane(0);
MM> ERROR:  unexpected SELECT query in exec_stmt_execsql()
MM> Can You help me find an error?
MM> tia

MM> CREATE FUNCTION mtldb_wykonane (integer) RETURNS integer AS  '
MM>    DECLARE
MM>       zlecenie alias for $1;
MM>       tls   integer;
MM>       twz   integer;
MM>    BEGIN
MM>       SELECT count(*) as ls FROM serwery;
MM>       tls:=new.ls;
MM>       SELECT count(*) as wz FROM wykonanie
MM>          WHERE zadanie_id=zlecenie AND kiedy IS NOT NULL;
MM>       twz:=new.wz;

MM>       if tls>twz
MM>          RETURN -1;
MM>       end if;
MM>       if tls=twz
MM>          RETURN 1;
MM>       end if;
MM>    END;
MM> ' LANGUAGE  'plpgsql' ;

Try

CREATE FUNCTION mtldb_wykonane (integer) RETURNS integer AS  '
   DECLARE
      zlecenie alias for $1;
       tls   integer;
       twz   integer;
    BEGIN
       SELECT count(*) as ls into tls FROM serwery;
       SELECT count(*) as wz into twz FROM wykonanie
          WHERE zadanie_id=zlecenie AND kiedy IS NOT NULL;

       if tls>twz
          RETURN -1;
       end if;
       if tls=twz
          RETURN 1;
       end if;
    END;
 ' LANGUAGE  'plpgsql' ;

BTW, "NEW" can be used only in trigger procedures.

--
Best regards,
 Yury  ICQ 11831432
 mailto:yura@vpcit.ru