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/ |
+---------------------------------------------------+
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly