[Mono-list] Npgsql.dll

[Carl Olsen]

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: mono-list-bounces at lists.ximian.com
[mailto:mono-list-bounces at lists.ximian.com] On Behalf Of Chris Aitken
Sent: Monday, May 23, 2005 4:09 AM
To: mono-list at lists.ximian.com
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  -  Mono-list at lists.ximian.com
http://lists.ximian.com/mailman/listinfo/mono-list