Thread: Problem in Stored Procedures

Hi all,

I m using Postgresql version 7.1.3-2.
I have written a function which accepts 2 arguments and returns matching
tuples from a table based on the arguments passed...but i am having problems
in getting it work.
This is my function -----

CREATE TYPE PointType AS(ParamId INTEGER,ParamName VARCHAR(5),Is_FixEnum
BIT,Is_ExpandEnum BIT);

CREATE FUNCTION PP_ReadParameter(integer,varchar) RETURNS setof PointType AS
'
DECLARErec PointType;
BEGINIF $1 IS NOT NULL THEN FOR rec IN SELECT ParamId, ParamName, Is_FixEnum, Is_ExpandEnum
AttributeId,
AttributeName,IsFixEnum,IsExpandEnum        FROM Attributes       WHERE AttributeId = $1       ORDER BY AttributeId
LOOP      RETURN NEXT rec;            END LOOP;             RETURN;END IF;ELSE IF $2 IS NOT NULL THEN     FOR rec IN
SELECTParamId, ParamName, Is_FixEnum,
 
Is_ExpandEnum                                   AttributeId,
AttributeName,IsFixEnum,IsExpandEnum               FROM Attributes              WHERE AttributeId = $2
ORDERBY AttributeId LOOP              RETURN NEXT rec;                   END LOOP;                    RETURN;ELSE
FORrec IN SELECT ParamId, ParamName, Is_FixEnum,
 
Is_ExpandEnum                                   AttributeId,
AttributeName,IsFixEnum,IsExpandEnum               FROM Attributes              ORDER BY AttributeId LOOP
RETURNNEXT rec;                   END LOOP;                    RETURN;END IF; END IF;
 

END;
' language 'plpgsql';



I get the error...
psql:Procedures.sql:2: ERROR: parse error at or near "AS" (for CREATE TYPE
command)
psql:Procedures.sql:40: NOTICE: return type 'pointtype' is only a shell
CREATE

WHEN I EXECUTE THE FUNCTION USING
SELECT (pp_readparameter(42,null));
ERROR: fmgr_info: function 0: cache lookup failed.


any value inputs on why this is happening.



> With Best Regards 
> Pradeep Kumar P J 
> 

Pradeepkumar, Pyatalo (IE10) wrote:

>Hi all,
>
>I m using Postgresql version 7.1.3-2.
>  
>

The create type and create function does work in PostgreSQL 7.4
without any modification. I guess the features you are trying to use
are not supported in 7.1.x


Regds
Mallah.

>I have written a function which accepts 2 arguments and returns matching
>tuples from a table based on the arguments passed...but i am having problems
>in getting it work.
>This is my function -----
>
>CREATE TYPE PointType AS(ParamId INTEGER,ParamName VARCHAR(5),Is_FixEnum
>BIT,Is_ExpandEnum BIT);
>
>CREATE FUNCTION PP_ReadParameter(integer,varchar) RETURNS setof PointType AS
>'
>DECLARE
> rec PointType;
>BEGIN
> IF $1 IS NOT NULL THEN
>  FOR rec IN SELECT ParamId, ParamName, Is_FixEnum, Is_ExpandEnum
>                             AttributeId,
>AttributeName,IsFixEnum,IsExpandEnum
>            FROM Attributes
>           WHERE AttributeId = $1
>           ORDER BY AttributeId LOOP
>           RETURN NEXT rec;
>             END LOOP;
>              RETURN;
> END IF;
> ELSE
>     IF $2 IS NOT NULL THEN 
>        FOR rec IN SELECT ParamId, ParamName, Is_FixEnum,
>Is_ExpandEnum
>                                       AttributeId,
>AttributeName,IsFixEnum,IsExpandEnum
>                   FROM Attributes
>                  WHERE AttributeId = $2
>                  ORDER BY AttributeId LOOP
>                  RETURN NEXT rec;
>                    END LOOP;
>                     RETURN;
>    ELSE
>        FOR rec IN SELECT ParamId, ParamName, Is_FixEnum,
>Is_ExpandEnum
>                                       AttributeId,
>AttributeName,IsFixEnum,IsExpandEnum
>                   FROM Attributes
>                  ORDER BY AttributeId LOOP
>                  RETURN NEXT rec;
>                    END LOOP;
>                     RETURN;
>    END IF;
>  END IF;
>
>END;
>' language 'plpgsql';
>
>
>
>I get the error...
>psql:Procedures.sql:2: ERROR: parse error at or near "AS" (for CREATE TYPE
>command)
>psql:Procedures.sql:40: NOTICE: return type 'pointtype' is only a shell
>CREATE
>
>WHEN I EXECUTE THE FUNCTION USING
>SELECT (pp_readparameter(42,null));
>ERROR: fmgr_info: function 0: cache lookup failed.
>
>
>any value inputs on why this is happening.
>
>
>
>  
>
>>With Best Regards 
>>Pradeep Kumar P J 
>>
>>    
>>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org
>
>  
>


-- 

regds
Mallah.

Rajesh Kumar Mallah
+---------------------------------------------------+
| Tradeindia.com  (3,11,246) Registered Users         | 
| Indias' Leading B2B eMarketPlace                  |
| http://www.tradeindia.com/                |
+---------------------------------------------------+