Need help creating a stored procedure - Mailing list pgsql-novice

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*.