Syntax error at or near "$1"

Started by Ryan Boozalmost 22 years ago2 messagesgeneral
Jump to latest
#1Ryan Booz
rxb19@psu.edu

I'm working with different functions and following an example of iterating
through rows from a returned query. It's not a complicated function, but I
can't get past this parse error. I've tried numerous things and searched
for some help. I can't find any reason for the error below:

ERROR: syntax error at or near "$1" at character 30
CONTEXT: PL/pgSQL function "list_devices" line 6 at for over select rows

Any help would be greatly appreciated!

Thanks,
Ryan

CREATE FUNCTION list_devices(macaddr) RETURNS text AS '
DECLARE
macAddress ALIAS FOR $1;
rowval record;
devices text;
BEGIN
devices := '' '';
FOR rowval IN SELECT device_type_id FROM devices WHERE mac_address =
macAddress LOOP
devices := devices || ''\r'';
END LOOP;
RETURN devices;
END;
' LANGUAGE 'plpgsql';

------------
Ryan J. Booz
Research Programmer
Penn State University
Information Technology Services
TLT/CLC
rxb19@psu.edu

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Ryan Booz (#1)
Re: Syntax error at or near "$1"

Ryan Booz <rxb19@psu.edu> writes:

I can't find any reason for the error below:

CVS tip gives a slightly more complete error message:

regression=# select list_devices('123456789012');
ERROR: syntax error at or near "$1" at character 30
QUERY: SELECT device_type_id FROM $1 WHERE mac_address = $2
CONTEXT: PL/pgSQL function "list_devices" line 7 at for over select rows
LINE 1: SELECT device_type_id FROM $1 WHERE mac_address = $2
^
regression=#

This might or might not help you figure out the problem: you used
"devices" as both a plpgsql variable name and a referenced table.
Pick another name for the variable.

regards, tom lane