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?
Carl Olsen http://www.carl-olsen.com/ -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Chris Aitken Sent: Monday, May 23, 2005 4:09 AM To: [email protected] Subject: RE: [Mono-list] Npgsql.dll > I have the page working better now, but the stored procedures > aren't doing what I want. I'm using an sql stored procedure > that returns a set of records. My function is called > senators() - ("select * from > senate_members") and it returns the entire table. The Npgsql > command text is "select field1(senators()), > field2(senators())" and it returns the information correctly. > I was just hoping I could write ("select field1, > field2 from senators") as the function instead of returning > the entire table. It seems like the stored procedures in > MSSQL 2000 are a lot better than the stored procedures in > PostgreSQL, but maybe I still don't know what I'm doing. I > could just as easily execute this query directly on my > database and return only the two fields I need. The stored > procedure isn't giving me any increase in efficiency, and it > actually appears to be less efficient. This is a PostgreSQL function written in SQL. If you get really bored, you could rewrite it in PL/pgSQL - although then you'd need to define a type to hold the record, then write the function. But as Francisco says - "select field1, field2 from senators()" should work. Examples: CREATE FUNCTION company() RETURNS setof tblcustomerinformation AS 'select * from tblcustomerinformation;' LANGUAGE SQL To run the stored procedure: "select * from company();" - all recordsets returned. (1) To select only customername & postcode: select customername,postcode from company(); (2) this runs the stored procedure, selecting ALL the information, and then filters out just the cusotomername & postcode. So running the sproc at (2) is as intensive as running the sproc at (1). As mentioned above - you could use a PL/PgSQL function. Create the type first (customername & postcode as both varchar in my table tblcustomerinformation): CREATE TYPE tp_company AS (customername varchar, postcode varchar); Now create the function: -- Function: plpgsql_company() -- DROP FUNCTION plpgsql_company(); CREATE OR REPLACE 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; Unfortunartely this is the only way to directly return explicit columns from a table via a sproc. HTH Chris -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. _______________________________________________ Mono-list maillist - [email protected] http://lists.ximian.com/mailman/listinfo/mono-list _______________________________________________ Mono-list maillist - [email protected] http://lists.ximian.com/mailman/listinfo/mono-list
