SET Statement - SQL Server to PostgreSQL Migration

In SQL Server, the SET statement assigns a value to a variable. In PostgreSQL, you can use := operator.

SQL Server:

  DECLARE @var VARCHAR(30);  
  SET @var = 'Hello, world!'

PostgreSQL:

  DO $$
  DECLARE var VARCHAR(30);
  BEGIN
    var := 'Hello, world!'; 
  END$$;

Assign Value from User-Defined Function (UDF)

Consider a user-defined function:

SQL Server:

  CREATE FUNCTION helloWorld() RETURNS VARCHAR(30)
  BEGIN
     RETURN 'Hello, world!'
   END;

Now you can call this function and assign the return value as follows:

  DECLARE @var VARCHAR(30);  
  SET @var = dbo.helloWorld()
 
  -- Using SELECT
  SET @var = (SELECT dbo.helloWorld())

The same function in PostgreSQL:

PostgreSQL:

  CREATE OR REPLACE FUNCTION helloWorld() RETURNS VARCHAR(30)
  AS $$
  BEGIN
     RETURN 'Hello, world!';
  END;
  $$ LANGUAGE plpgsql;

We can also call this function and assign the return value:

  DO $$
  DECLARE var VARCHAR(30);
  BEGIN  
    var := helloWorld();
 
    -- Using SELECT
    var := (SELECT helloWorld());
  END$$;