[Mono-list] Npgsql.dll

[Carl Olsen]

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: 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