Thread: Please help, can't figure out what's wrong with this function...
....
Hello group,
I 've written the following function:
CREATE OR REPLACE FUNCTION "public"."getstadtlandflussentrybyid" (integer) RETURNS SETOF "public"."ty_stadtlandflussentry" AS'
DECLARE objReturn ty_stadtlandflussentry;
DECLARE iid integer;
BEGIN
iid := $1;
for objReturn IN
SELECT ste_id, ste_type, ste_name, ste_firstwrongname, ste_secondwrongname, ste_description, ste_online
FROM tbl_stadtlandflussentry WHERE ste_id=iid
loop
RETURN next objReturn;
END LOOP;
RETURN;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
DECLARE objReturn ty_stadtlandflussentry;
DECLARE iid integer;
BEGIN
iid := $1;
for objReturn IN
SELECT ste_id, ste_type, ste_name, ste_firstwrongname, ste_secondwrongname, ste_description, ste_online
FROM tbl_stadtlandflussentry WHERE ste_id=iid
loop
RETURN next objReturn;
END LOOP;
RETURN;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
When I try to execute the function by calling
SELECT getstadtlandflussentrybyid(1);
I get the following error:
ERROR: missing .. at end of SQL expression
I haven't figured out what this message wants to tell me and why it is thrown at all.
Maybe someone can give me a hint,
Thanks in advance,
Moritz
PS: the function should run under postgres 7.4 and created the following type:
CREATE TYPE "public"."ty_stadtlandflussentry" AS (
"ste_id" BIGINT,
"ste_type" INTEGER,
"ste_name" VARCHAR(100),
"ste_firstwrongname" VARCHAR(100),
"ste_secondwrongname" VARCHAR(100),
"ste_description" TEXT,
"ste_online" INTEGER
);
"ste_id" BIGINT,
"ste_type" INTEGER,
"ste_name" VARCHAR(100),
"ste_firstwrongname" VARCHAR(100),
"ste_secondwrongname" VARCHAR(100),
"ste_description" TEXT,
"ste_online" INTEGER
);
On Sep 12, 2005, at 8:14 AM, Moritz Bayer wrote: > I get the following error: > ERROR: missing .. at end of SQL expression it looks like your for loop is being interpreted as the integer variant, e.g. for i in 1..10 loop > CREATE TYPE "public"."ty_stadtlandflussentry" AS ( > DECLARE objReturn ty_stadtlandflussentry; Maybe it needs to be: declare objReturn "public"."ty_stadtlandflussentry"%rowtype; John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
Moritz Bayer <moritz.bayer@googlemail.com> writes:
> I get the following error:
> ERROR: missing .. at end of SQL expression
> I haven't figured out what this message wants to tell me and why it is
> thrown at all.
I think it's telling you that you are using a 7.3 or older server.
Try 7.4 or later --- plpgsql was pretty weak on handling rowtype
variables that far back.
regards, tom lane