This is a working function in SQL Server 2014 Express

CREATE FUNCTION [dbo].[Func_Account_FollowingCustomer]
    (@AccountNumber NVARCHAR(20))
RETURNS BIT
AS
BEGIN       
    DECLARE @bResult BIT

    IF (SELECT COUNT(AccountNumber) FROM dbo.Account AS A 
        WHERE DetailByAccountObject = 1 
          AND AccountObjectType = 1 
          AND AccountNumber = @AccountNumber) > 0
        SET @bResult = 1
    ELSE
        SET @bResult = 0

    RETURN @bResult
END

I try to convert to PostgreSQL 14, I have

CREATE FUNCTION public.func_account_following_customer(IN account_number character varying)
    RETURNS bit
    LANGUAGE 'sql'
    

declare @b_result bit
begin
if(select count(account_number) from account as a where detail_by_account_object = 1 and account_object_type = 1 and account_number = @account_number) > 0
    set @b_result = 1
else
    set @b_result = 0
return @b_result;
end;

ALTER FUNCTION public.func_account_following_customer(character varying)
    OWNER TO postgres;

Error

ERROR: syntax error at or near "declare" LINE 5: declare @b_result bit ^

How to fix it?

CodePudding user response：

language sql can't use variables or procedural elements (like IF), you need language plpgsql - but the syntax for variable names is different and the assignment is not done using set

The function body is a string constant, typically specified using dollar quoting.

If you want to return true/false flags, use boolean instead of bits.

Parameters or variables are referenced using @ but simply with their name.

But you don't need procedural code for such a simple SQL query that just returns true/false.

CREATE FUNCTION public.func_account_following_customer(IN p_account_number character varying)
    RETURNS boolean
    LANGUAGE sql
as
$$
  select count(*) > 0 
  from account as a 
  where detail_by_account_object = 1 
    and account_object_type = 1 
    and account_number = p_account_number;
$$
;

As a PL/pgSQL function this would be:

CREATE FUNCTION public.func_account_following_customer(IN p_account_number character varying)
    RETURNS boolean
    LANGUAGE plpgsql
as
$$
declare
  l_result boolean;
begin
  if (select count(*)
      from account as a 
      where detail_by_account_object = 1 
        and account_object_type = 1 
        and account_number = p_account_number) > 0 
  then 
    l_result := true;
  else
    l_result := false;
  end if;
  return l_result;
end;
$$
;