ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function "sp_getstatelist" line 6 at return next
My table is "Accounts_StateCodes" The fields are "Description","StateCode","Region" The types are varchar(60),char(2),varchar(60) CREATE TYPE StateCodes AS ( Description varchar, StateCode varchar); DECLARE r StateCodes%ROWTYPE; BEGIN FOR r IN SELECT "Description","StateCode" FROM "Accounts_StateCodes" LOOP RETURN NEXT r; END LOOP; RETURN; END; -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Chris Aitken Sent: Tuesday, June 07, 2005 9:24 AM To: [EMAIL PROTECTED]; mono-list@lists.ximian.com Subject: RE: [Mono-list] Npgsql.dll > It's me again, back to try and write some SPROC. I want to > try the second method (PL/PgSQL), but I'm assuming you get > back two arrays each with the same index, one for the > customername and one for the postcode. Is that how you > capture the returned data in a dataset? > Hi Carl, If you set up as per the original example. It will return a set - i.e. a table, much the same as a pure SQL query. My example here returns (within pgAdminIII): Row customername(varchar) postcode(varchar) 1 data data 2 data data ... ... N data data Obviously the above is substituted for the actual data (apart from row numbers). As I mentioned, is is returning a set of 'type', where in this case the 'type' is called 'tp_company' is 2 columns of varchar, one named customername, the other postcode. The type must be defined first: CREATE TYPE tp_company AS (customername varchar, postcode varchar); Now the actual SPROC is written: CREATE FUNCTION plpgsql_company() RETURNS SETOF tp_company AS ' DECLARE r tp_company%rowtype; BEGIN FOR r IN SELECT customername,postcode FROM tblcustomerinformation LOOP RETURN NEXT r; END LOOP; RETURN; END ' LANGUAGE 'plpgsql' VOLATILE; As can be seen, the function is defined as returning a set of 'tp_company'. We then delare 'r' as being a row of 'tp_company'. Then for each 'r' (row of 'tp_company', remember) in the SQL query, return it, then loop until the end. Return the whole shebang. End Hope this helps. If you want me to write you an example for your data, let me know which columns you want, table names & column types. Chris -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. _______________________________________________ Mono-list maillist - Mono-list@lists.ximian.com http://lists.ximian.com/mailman/listinfo/mono-list _______________________________________________ Mono-list maillist - Mono-list@lists.ximian.com http://lists.ximian.com/mailman/listinfo/mono-list