Function compile error - Mailing list pgsql-novice
From | Steve Tucknott |
---|---|
Subject | Function compile error |
Date | |
Msg-id | 40F842C0.4090600@retsol.co.uk Whole thread Raw |
Responses |
Re: Function compile error
|
List | pgsql-novice |
Can anyone help with this? I'm new to Postgre and plpgsql - but have some experience of sql/plsql and Informix procedures. I have converted an Informix procedure to plpgsql - and get the following error when the function is executed: NOTICE: plpgsql: ERROR during compile of proc_allowed near line 3 ERROR: parse error at or near "AS" The procedure is attached. CREATE FUNCTION proc_allowed ( CHAR, VARCHAR, INTEGER, INTEGER ) RETURNS INTEGER AS ' DECLARE p_key ALIAS AS $1; p_foreignTableName ALIAS AS $2; p_foreignRecNo ALIAS AS $3; p_usersRecNo ALIAS AS $4; l_access CHAR(1); l_defaultAccess CHAR(1); l_true INTEGER; l_false INTEGER; l_sqlStatus INTEGER; l_isamStatus INTEGER; BEGIN l_access := NULL; l_defaultAccess := NULL; l_true := 1; l_false := 0; -- If there are no entries on the perm table allow all SELECT defaultPermission INTO l_defaultAccess FROM accessPermsHdr ph WHERE ph.key = p_key AND ph.foreignTableName = p_foreignTableName AND ph.foreignRecNo = p_foreignRecNo; IF l_defaultAccess IS NULL THEN RETURN l_true; END IF; -- Check for specific entry for the user SELECT MAX(pd.defaultPermission) INTO l_access FROM accessPermsDet pd, accessPermsHdr ph WHERE pd.usersRecNo = p_usersRecNo AND ph.key = p_key AND pd.accessPermsHdrRecNo = ph.recNo; IF l_access IS NOT NULL THEN IF l_access = ''A'' THEN RETURN l_true; ELSE RETURN l_false; END IF; END IF; -- Check for general entry for the users roles FOR l_access IN SELECT pd.defaultPermission FROM accessPermsDet AS pd ,accessPermsHdr AS ph, usersRoles AS uRole WHERE pd.lookUpCodesRecNo = uRole.lookUpCodesRecNo AND uRole.usersRecNo = p_usersRecNo AND pd.accessPermsHdrRecNo = ph.recNo AND ph.key = p_key ORDER BY 1 LOOP EXIT; END LOOP IF l_access IS NOT NULL THEN IF l_access = ''A'' THEN RETURN l_true; ELSE RETURN l_false; END IF; END IF; -- Return default access read initially .. IF l_defaultAccess = ''A'' THEN RETURN l_true; ELSE RETURN l_false; END IF; END; ' LANGUAGE 'plpgsql';
pgsql-novice by date: