Thread: Calling stored procedures.

Hi.  I've created some stored procedures. For example:

create function validate_user(varchar, varchar)
RETURNS integer AS '
BEGIN
SELECT count(*) INTO ret
FROM  users
WHERE userid = $1 and passwd = $2;
RETURN ret;
END;
'
 LANGUAGE 'plpgsql';

When I try to call it with the command, select validate_users('user',
passwd'), I get this error message:

Error Number: -2147467259
Error Description: Error while executing the query;
ERROR:  fmgr_info: function 19264: cache lookup failed

I did some research and found that this error message occurs when a stored
procedure refers to another function that has been dropped from the database
and that to avoid this error you must drop all objects referring to this
stored procedure and recreate them.  The problem is that I get this error
message for NEWLY created procedures?  Any ideas about this?  I don't know
what this OID 19264 is referring to.

Thanks.

John

John Trambakoulos wrote:

>
> Error Number: -2147467259
> Error Description: Error while executing the query;
> ERROR:  fmgr_info: function 19264: cache lookup failed
>
> I did some research and found that this error message occurs when a stored
> procedure refers to another function that has been dropped from the database
> and that to avoid this error you must drop all objects referring to this
> stored procedure and recreate them.  The problem is that I get this error
> message for NEWLY created procedures?  Any ideas about this?  I don't know
> what this OID 19264 is referring to.


I believe this is because you have an undeclared variable ret. Try to
declare it like below.

create function validate_user(varchar, varchar)
RETURNS integer AS '
DECLARE ret INTEGER;
BEGIN
   SELECT count(*) INTO ret
   FROM  users
   WHERE userid = $1 and passwd = $2;
   RETURN ret;
END;
'
LANGUAGE 'plpgsql';

Jochem

You need to declare the variable "ret", i.e. add a line before "BEGIN" like:
DECLARE ret integer;
Your function will not work without that.
You may still get an error for some other unknown reason, or you may not.
I have found that plpgsql errors can be highly uninformative, or downright
misleading.



Hi.  I've created some stored procedures. For example:

create function validate_user(varchar, varchar)
RETURNS integer AS '
BEGIN
SELECT count(*) INTO ret
FROM  users
WHERE userid = $1 and passwd = $2;
RETURN ret;
END;
'
 LANGUAGE 'plpgsql';

When I try to call it with the command, select validate_users('user',
passwd'), I get this error message:

Error Number: -2147467259
Error Description: Error while executing the query;
ERROR:  fmgr_info: function 19264: cache lookup failed

I did some research and found that this error message occurs when a stored
procedure refers to another function that has been dropped from the database
and that to avoid this error you must drop all objects referring to this
stored procedure and recreate them.  The problem is that I get this error
message for NEWLY created procedures?  Any ideas about this?  I don't know
what this OID 19264 is referring to.

Thanks.

John