Thread: plpgsql function

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

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

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

Marcin Mazurek

--
Kierownik Działu Systemowego
MULTINET SA o/Poznan
http://www.multinet.pl/

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

Yury Don (yura@vpcit.ru) napisał:

thx, reading docs helps a lot:) It's a pity that there are almost no
examples in decription of pl/pgsql.
I'vefound something helpfull ./src/pl/plgsql/test I think it would help some
other people to mention it in docs.

btw. where postgresql team considering creating of pgsql-announce?

thx
maz

Marcin Mazurek

--
Kierownik Działu Systemowego
MULTINET SA o/Poznan
http://www.multinet.pl/