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(at)vpcit(dot)ru