i've just migrated a stored procedure from MySQl to PostgreSQL and i have the following error :
ERROR: syntax error at or near "SELECT" at character 371
here is my function in PostgreSQL :
CREATE OR REPLACE FUNCTION immense_sp001(VARCHAR,VARCHAR, service_nom VARCHAR, OUT result BOOLEAN, OUT error_message VARCHAR)
RETURNS record LANGUAGE plpgsql
AS '
BEGIN
DECLARE
username varchar :=$1;
strhash varchar :=$2;
Profile_Detected INTEGER;
Service_Already_Exist INTEGER;
/* detect if the user logged in exists in database */
SELECT count(*) INTO Profile_Detected FROM profiles WHERE login=username AND pwd=strhash;
if (Profile_Detected = 1) then
/* detect if service already exists in database */
SELECT count(*)
INTO Service_Already_Exist
FROM immense.services
WHERE service_nom = service_name;
if (Service_Already_Exist = 0) then
/* to reset AUTO_INCREMENT field : service_id */
alter table services auto_increment=1;
/* service does not exist, so we can add it */
insert into immense.services
set service_name = service_nom;
set result = false;
set error_message="new service created";
else
/* service already exists in database and can not be created */
set result = true;
set error_message = "service already exists";
end if;
else
set result=true;
set error_message = "user does not exist";
end if;
END;
'
-----
What could it be ? I'm lost :-(
thanks for help.
Maileen
New Yahoo! Messenger with Voice. Call regular phones from your PC and save big.
