pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem in Stored Procedures
2+ messages / 2 participants
[nested] [flat]
* Problem in Stored Procedures
@ 2004-07-10 06:31 Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>
0 siblings, 1 reply; 2+ messages in thread
From: Pradeepkumar, Pyatalo (IE10) @ 2004-07-10 06:31 UTC (permalink / raw)
To: 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
>
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Problem in Stored Procedures
@ 2004-07-11 06:35 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>
0 siblings, 0 replies; 2+ messages in thread
From: Rajesh Kumar Mallah @ 2004-07-11 06:35 UTC (permalink / raw)
To: Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>; +Cc: pgsql-sql
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/ |
+---------------------------------------------------+
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-07-11 06:35 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-10 06:31 Problem in Stored Procedures Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>
2004-07-11 06:35 ` Rajesh Kumar Mallah <mallah@trade-india.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox