Problem in Stored Procedures

From: "Pradeepkumar, Pyatalo (IE10)" <Pradeepkumar(dot)Pyatalo(at)honeywell(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Problem in Stored Procedures
Date: 2004-07-10 06:31:39
Message-ID: 77ED2BF75D59D1439F90412CC5B109740EABEFC8@ie10-sahara.hiso.honeywell.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql


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
'
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
>

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Rajesh Kumar Mallah 2004-07-11 06:35:10 Re: Problem in Stored Procedures
Previous Message Kris Jurka 2004-07-09 23:55:07 Re: ResultSerMetaData.getColumnDisplaySize() with timestamp