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

Question

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?

This kind of thing isn't standardised or portable; you can't just change keywords to lower-case and expect it to work in a completely different DBMS. — IMSoP, Jun 20 '22 at 15:13

score 3 · Accepted Answer · answered Jun 20 '22 at 15:11

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

Can you create function via pgAdmin's GUI? I use GUI for create function, but cannot output SQL script like you. — Vy Do, Jun 20 '22 at 15:15
I don't use pgAdmin - but you can run any SQL statement you want, through the "Query tool". You need to store the source code of that function somewhere in git anyway, so sooner or later you need the full CREATE FUNCTION statement anyway. So why not start with it directly. — , Jun 20 '22 at 15:18
When you have time and tool, please guide me via pgAdmin GUI. I want use GUI for official syntax generated by tool. https://user-images.githubusercontent.com/1328316/174634350-86b71e51-d1ec-4187-97ca-044de8a2dde8.mp4 — Vy Do, Jun 20 '22 at 15:24

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

1 Answers1