Parser error, why?

roberm1 · Mar 18, 2003

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 · Mar 18, 2003

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 · Mar 18, 2003

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 · Mar 19, 2003

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 · Mar 24, 2003

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 · Apr 15, 2003

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.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Parser error, why?

roberm1

Programmer

rosenk

Programmer

roberm1

Programmer

ceco

IS-IT--Management

newbiepg

Programmer

uniware

Programmer

Similar threads

Part and Inventory Search

Sponsor