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