I'm using the first method now (the one using SQL), but I'll give the second one a try (the one usi9ng PL/pgSQL). The first one seems to work okay, because I can write overloaded versions that take different parameters.
Thank you! Carl -----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
