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  (Oliver Elphick <olly@lfix.co.uk>)
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:

Previous
From: mmarencibia@yahoo.es (Marcos Medina)
Date:
Subject: FOR-IN-EXECUTE, why fail?
Next
From: Oliver Elphick
Date:
Subject: Re: Function compile error