Thread: FW: need help on stored procedures

> Hi all,
>
> 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.
>
> Please help me on this
>
>
>     With Best Regards
>     Pradeep Kumar P J
>

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.

When you send the query to be parsed I presume you must somehow tell
which elements of it are parameters.  How do you do this?

e.g.:   SELECT name, phone FROM friends WHERE age > 25;

How would I write this if "name" and "25" must be parameters?  Or if
the operator ">" should be a parameter or modifiable in another way?

(Is there a good tutorial site on the extended query language?  It
seems there's almost no information about this on the net.)

Thanks,

Marc

At 4:26 PM +0200 7/8/04, M. Bastin wrote:
>When you send the query to be parsed I presume you must somehow tell
>which elements of it are parameters.  How do you do this?
>
>e.g.:   SELECT name, phone FROM friends WHERE age > 25;
>
>How would I write this if "name" and "25" must be parameters?  Or if
>the operator ">" should be a parameter or modifiable in another way?

Can "age > 25" as a whole be a parameter?

Thanks,

Marc

"M. Bastin" <marcbastin@mindspring.com> writes:
> At 4:26 PM +0200 7/8/04, M. Bastin wrote:
>> When you send the query to be parsed I presume you must somehow tell
>> which elements of it are parameters.  How do you do this?
>> e.g.:   SELECT name, phone FROM friends WHERE age > 25;
>> How would I write this if "name" and "25" must be parameters?

    SELECT name, phone FROM friends WHERE $1 > $2;

>> Or if
>> the operator ">" should be a parameter or modifiable in another way?

You cannot make an operator a parameter.  It's not very clear what it
would mean to prepare a query in which some operators remain unknown ---
certainly the planner could not produce any useful plan for it.

> Can "age > 25" as a whole be a parameter?

Only if you are prepared to supply a boolean value for it at Bind time.

            regards, tom lane