Thread: Function compile error

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';

On Fri, 2004-07-16 at 22:04, Steve Tucknott wrote:
> 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
...
>      DECLARE
>        p_key                           ALIAS AS $1;

Should be: ALIAS FOR $1

--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
     "For God so loved the world, that he gave his only
      begotten Son, that whosoever believeth in him should
      not perish, but have everlasting life."     John 3:16