Обсуждение: function runs on Windows not on solaris
Hi
I am using PostgreSQL 7.3.2 on Solaris 8 machine.
I have written following procedure for Windows ( testing purpose ) and solaris ( production ). It works well on windows but gives following error on solaris. Can anyone help me to find out what is the problem ?
CREATE OR REPLACE FUNCTION p_MapLicKeyToAccount(VARCHAR,VARCHAR) RETURNS INT4 AS '
DECLARE
licKey ALIAS FOR $1;
accountId ALIAS FOR $2;
licKey ALIAS FOR $1;
accountId ALIAS FOR $2;
checkLicKey VARCHAR DEFAULT NULL;
checkAcctId VARCHAR DEFAULT NULL;
checkAcctId VARCHAR DEFAULT NULL;
BEGIN
IF accountId ISNULL THEN
RETURN 1;
ELSE
SELECT INTO checkLicKey license_key FROM db_license_tab WHERE license_key=licKey;
IF checkLicKey ISNULL THEN
RAISE NOTICE ''License Key % does not exists'',licKey;
RETURN 2;
END IF;
SELECT INTO checkAcctId account_id FROM db_license_tab WHERE license_key=licKey;
IF accountId ISNULL THEN
RETURN 1;
ELSE
SELECT INTO checkLicKey license_key FROM db_license_tab WHERE license_key=licKey;
IF checkLicKey ISNULL THEN
RAISE NOTICE ''License Key % does not exists'',licKey;
RETURN 2;
END IF;
SELECT INTO checkAcctId account_id FROM db_license_tab WHERE license_key=licKey;
IF checkAcctId NOTNULL THEN
RAISE NOTICE ''License Key % already used'',licKey;
RETURN 3;
END IF;
checkAcctId = NULL;
SELECT INTO checkAcctId account_id FROM db_acctmaster_tab WHERE account_id=accountId;
IF checkAcctId NOTNULL THEN
RAISE NOTICE ''Account id % already exists'',accountId;
RETURN 4;
END IF;
RAISE NOTICE ''License Key % already used'',licKey;
RETURN 3;
END IF;
checkAcctId = NULL;
SELECT INTO checkAcctId account_id FROM db_acctmaster_tab WHERE account_id=accountId;
IF checkAcctId NOTNULL THEN
RAISE NOTICE ''Account id % already exists'',accountId;
RETURN 4;
END IF;
UPDATE db_license_tab SET account_id=accountId,license_anniversarydate=current_date(),license_status=1 WHERE license_key=licKey;
END IF;
END IF;
RETURN 0;
END
END
' LANGUAGE 'plpgsql';
Error:
MYDB# select p_MapLicKeyToAccount('1B5D892169812C6F','testsuraj@max.com');
WARNING: Error occurred while executing PL/pgSQL function p_maplickeytoaccount
WARNING: line 36 at SQL statement
ERROR: parser: parse error at or near "(" at character 80
WARNING: Error occurred while executing PL/pgSQL function p_maplickeytoaccount
WARNING: line 36 at SQL statement
ERROR: parser: parse error at or near "(" at character 80
Thanks & Regards,
Suraj
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
suraj mundada <surajmundada@yahoo.com> writes:
> UPDATE db_license_tab SET account_id=accountId,license_anniversarydate=current_date(),license_status=1 WHERE
license_key=licKey;
> ERROR: parser: parse error at or near "(" at character 80
I believe CURRENT_DATE is supposed to be called without parentheses.
regards, tom lane