Hello all,
I am attempting to create a function and am receiving the following error
when attempting to access the function:
------------------------8<------------------------------------------
SELECT * FROM "MySchema"."tester"(3);
ERROR: wrong record type supplied in RETURN NEXT
CONTEXT: PL/pgSQL function "tester" line 7 at return next
------------------------8<------------------------------------------
I only receive the error when I specify a number smaller than the largest
value in the OfferID field (see below). If I specify a value larger than is
in the OfferID field, the function returns 0 rows, but does not error. I
must be missing something simple here and would appreciate some direction.
Here is the table structure:
------------------------8<------------------------------------------
CREATE TABLE "MySchema"."tblTransact"
(
"TransactID" int8 NOT NULL DEFAULT
nextval('"MySchema"."tblTransact_TransactID_seq"'::text),
"TDate" timestamptz NOT NULL,
"DestContactID" int4 NOT NULL,
"OfferID" int8 NOT NULL,
"TransactStatus" int2 NOT NULL,
"TSearchTerm" varchar(64) NOT NULL,
"SubscriptionID" int4 NOT NULL DEFAULT 0,
CONSTRAINT "pk_TransactID" PRIMARY KEY ("TransactID")
)
WITH OIDS;
------------------------8<------------------------------------------
BTW, there is data in the table that meets the criteria I specify.
Below is my function definition. This is not actually the SELECT statement
I hope to include, but is merely a test to try to create a function that
returns multiple rows. I have seen some postings of folks using a table
reference as a return structure template (and tested this with their example
structure and it does seem to work). I sure hope I do not have to do one of
the following, but may have to:
1) specify the data types in my function call
2) do casting in the function call (I did try this-- SELECT * FROM
"MySchema"."tester"('3'::int8);
3) create a new 'type' for the return structure as opposed to specifying an
existing table
------------------------8<------------------------------------------
CREATE OR REPLACE FUNCTION "MySchema".tester(int8)
RETURNS SETOF "MySchema"."tblTransact" AS
'DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT * FROM "MySchema"."tblTransact"
WHERE "OfferID" > $1
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;'
LANGUAGE 'plpgsql' VOLATILE;
------------------------8<------------------------------------------
Thanks in advance.
Cheers,
Jim
_________________________________________________________________
Take charge with a pop-up guard built on patented Microsoft� SmartScreen
Technology
http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines
Start enjoying all the benefits of MSN� Premium right now and get the
first two months FREE*.