chris at ion-dreams.com
Mon May 23 05:09:20 EDT 2005
> 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.
CREATE FUNCTION company() RETURNS setof tblcustomerinformation
AS 'select * from tblcustomerinformation;'
To run the stored procedure: "select * from company();" - all recordsets
To select only customername & postcode: select customername,postcode from
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
CREATE TYPE tp_company AS
Now create the function:
-- Function: plpgsql_company()
-- DROP FUNCTION plpgsql_company();
CREATE OR REPLACE FUNCTION plpgsql_company()
RETURNS SETOF tp_company AS
FOR r IN SELECT customername,postcode FROM tblcustomerinformation
RETURN NEXT r;
LANGUAGE 'plpgsql' VOLATILE;
Unfortunartely this is the only way to directly return explicit columns from
a table via a sproc.
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
More information about the Mono-list