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
>
