Parser error, why?

[roberm1]

Hi, below is my function, for some reason when trying to use the function, it says there is a 'Parse error at or new "1$", does anyone know why it is saying this because it looks ok to me ? Please see below:


CREATE FUNCTION newcalls(varchar, varchar, varchar, varchar, varchar, varchar, varchar) RETURNS varchar AS 'DECLARE
locationin ALIAS FOR $1;
ctype ALIAS FOR $2;
engineer ALIAS FOR $3;
cstatus ALIAS FOR $4;
notes ALIAS FOR $5;
entby ALIAS FOR $6;
eq ALIAS FOR $7;
locid INTEGER;
calltid INTEGER;
engid INTEGER;
status INTEGER;
enttime DATETIME;
entbyid INTEGER;
eqid INTEGER;

BEGIN
locid = (select id from location where ward = locationin);
calltid = (select id from type where type = ctype);
engid = (select id from users where lastname = engineer);
status = (select id from status where status = cstatus);
enttime = (getdate());
entbyid = (select id from users where lastname = entby);
eqid = (select id from equipment where equipment = eq);
INSERT INTO CallLog.calls(location, type, eng, status, timeenter, notes, enteredby, equipment, enterbyn, timecomp) values(locid, calltid, engid, status, enttime, notes, entbyid, eqid, entbyid, enttime);

END;
' LANGUAGE 'plpgsql';

Thanks in Advance, Mark.

 

[rosenk]

Weird error message, but I think the problem is because assignment to variables is done with [tt]:=[/tt]

[tt]locid := (select id from ...);[/tt]

 

[roberm1]

Thanks for your swift response 'rosenk', I have tried your suggestion, but unfortunately nothing has changed the error message is the same, thanks anyways, anymore suggestions?

Regards Mark.

 

[ceco]

i don't see any obvious error, only strange that the error is 1$, and not $1 for example,

but if there is a rule or trigger on some of the tables that are 'touched' by the function, and the function called by the trigger (for example before insert on CallLog.calls) for example may issue this error

 

[newbiepg]

do not call
locid = (select id from location where ward = locationin);

and so on

you should create separate functions for them
and call these functions using perform

The problem is that locid ,calltid and the rest of the variables declared are sql statements and not integers or datetime types.They could for instance return a column.

To check if this is the error

provide static values to these variables

e.g.

engid := 2;
locid := 5;

and so on
try this out and see if it works

 

[uniware]

What's your PostgreSQL's version? You may get it from psql
using 'select version()'.

In 7.3 I find there are no data type called DATETIME, use
TIMESTAMP instead.

Another thing is that you should use := to do assignment of
variables. 'SELECT col INTO var' should be used to assign
select results into variables.

Wish this can help.