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.