scalar function

MS SQL:

 CREATE FUNCTION FormatterNumber(@i INT) RETURNS VARCHAR(100)
 AS
 BEGIN
  RETURN CONVERT(VARCHAR, @i) + ' Michael the INT'
 END
 GO
 SELECT DBO.FormatterNumber(1)

PostgreSQL: Note postresql parameter can be also be referenced by their parameter number, e.g. $1, $2..

 -- we don't have to declare varchar(100) in returns clause,
 -- it is adjusted dynamically
 
 -- method 1, this is flexible, can have multiple statements between begin and end
 CREATE FUNCTION FormatterNumber(m_I INT) RETURNS VARCHAR
 AS
 $$
 BEGIN
  RETURN m_I::VARCHAR || ' INT';
  -- RETURN $1::VARCHAR || ' INT'; -- this will work too
 END;
 $$
 LANGUAGE 'plpgsql';



 -- method 2, just change the language 'plpgsql' to 'sql',
 -- this is rigid, only one statement is allowed,
 -- though seems this is much faster(haven't yet benchmark against method 1)

 -- somehow the disadvantage of language 'sql' is you can't refer the paramater
 -- by its name, only by parameter position

 CREATE FUNCTION FormatterNumber(m_I INT) RETURNS VARCHAR
 AS
 $$
  SELECT $1::VARCHAR || ' Michael the INT';
 $$
 LANGUAGE 'sql';



 invocation sample:

 SELECT FormatterNumber(1);






MS SQL:

 CREATE FUNCTION FormatterDate(@d DATETIME) RETURNS VARCHAR(100)
 AS
 BEGIN
  RETURN CONVERT(VARCHAR, @d) + ' Michael the date'
 END
 GO

 SELECT DBO.FormatterDate('November 5 2006')


PostgreSQL:

  

 -- method 1
 CREATE OR REPLACE FUNCTION FormatterDate(m_D DATE) RETURNS VARCHAR
 AS
 $$
 BEGIN
  RETURN m_D::VARCHAR || ' Michael the date';
 END;
 $$
 LANGUAGE 'plpgsql';



 -- method 2
 CREATE OR REPLACE FUNCTION FormatterDate(m_D DATE) RETURNS VARCHAR
 AS
 $$
  SELECT $1::VARCHAR || ' Michael the date';
 $$
 LANGUAGE 'plpgsql';


 SELECT FormatterDate('November 5 2006')

PostgreSQL is much flexible in its function, you can overload function name,
you can have two function with same name,
resulting to a more symmetrical usage of function

 -- method 1
 CREATE OR REPLACE FUNCTION Formatter(m_I INT) RETURNS VARCHAR
 AS
 $$
 BEGIN
  RETURN m_D::VARCHAR || ' Michael the INT';
 END;
 $$
 LANGUAGE 'plpgsql';



 -- method 2
 CREATE OR REPLACE FUNCTION Formatter(m_D DATE) RETURNS VARCHAR
 AS
 $$
  SELECT $1::VARCHAR || ' Michael the date';
 $$
 LANGUAGE 'plpgsql';



 SELECT Formatter(1);
 SELECT Formatter('nov 5 2006');