Re: FW: need help on stored procedures

On Wed, 7 Jul 2004, Pradeepkumar, Pyatalo (IE10) wrote:

> > I have written a sample procedure where i pass 2 arguments. Based on the
> > arguments i need to select few fields from a table. After selecting the
> > fields i have to display them. How do i return the selected fields. The
> > procedure is as follows
> >
> > CREATE FUNCTION PP_ReadParameter(integer,varchar) RETURNS TEXT AS '
> > DECLARE
> > ParamId INTEGER;
> > ParamName TEXT;
> > IsFixEnum BIT;
> > IsExpandEnum BIT;
> > BEGIN
> > IF $1 IS NOT NULL THEN
> > SELECT INTO ParamId,ParamName,IsFixEnum,IsExpandEnum
> > AttributeId,AttributeName,IsFixEnum,IsExpandEnum
> > FROM Attributes
> > WHERE AttributeId = $1
> > ORDER BY AttributeId;
> >
> > RETURN ''$1 successfull'';
> > END IF;
> > ......
> > ......
> > END;
> > ' LANGUAGE 'plpgsql';
> >
> > So when i say SELECT PP_ReadParameter(50,Null)......it should return the
> > ParamId,ParamName,....
> > But to check the working of the function i just return ''$1 successfull''
> > as i dont know how to return the tuple.

There's a question of whether you expect this to return one row or
multiple rows. I'm guessing multiple rows, so...

Something of the general form:

CREATE TYPE newtype AS (ParamId ...);
CREATE FUNCTION PP_ReadParameter(integer,varchar) RETURNS setof newtype AS
'
DECLARE
rec newtype;
BEGIN
IF $1 IS NOT NULL THEN
FOR rec IN SELECT ParamId, ParamName, IsFixEnum, IsExpandEnum,
AttributeId, AttributeName
FROM Attributes
WHERE AttributeId = $1
ORDER BY AttributeId LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END IF;
...
END;' language 'plpgsql';

General Bits (http://www.varlena.com/varlena/GeneralBits/) and
techdocs.postgresql.org have some documents on returning sets from
functions that you might want to look at.