X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9ECB8D1B1D5 for ; Sun, 11 Jul 2004 03:35:16 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 07303-09 for ; Sun, 11 Jul 2004 06:35:18 +0000 (GMT) Received: from trade-india.com (unknown [61.16.154.82]) by svr1.postgresql.org (Postfix) with SMTP id 7C675D1B1B6 for ; Sun, 11 Jul 2004 03:35:10 -0300 (ADT) Received: (qmail 16097 invoked from network); 11 Jul 2004 06:33:53 -0000 Received: from system100.trade-india-local.com (HELO ?192.168.0.100?) (192.168.0.100) by system66.trade-india-local.com with SMTP; 11 Jul 2004 06:33:53 -0000 Message-ID: <40F0DF9E.7060204@trade-india.com> Date: Sun, 11 Jul 2004 12:05:10 +0530 From: Rajesh Kumar Mallah User-Agent: Mozilla Thunderbird 0.6 (X11/20040502) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Pradeepkumar, Pyatalo (IE10)" Cc: pgsql-sql@postgresql.org Subject: Re: Problem in Stored Procedures References: <77ED2BF75D59D1439F90412CC5B109740EABEFC8@ie10-sahara.hiso.honeywell.com> In-Reply-To: <77ED2BF75D59D1439F90412CC5B109740EABEFC8@ie10-sahara.hiso.honeywell.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200407/58 X-Sequence-Number: 18310 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/ | +---------------------------------------------------+