Functions - RETURNS TABLE - SQL Server to PostgreSQL Migration

In SQL Server, a user-defined function can return a table (it is called table-valued function). PostgreSQL also allows you to return a table from a function:

SQL Server:

  -- Table-valued function
  CREATE FUNCTION fn_get_cities()
    RETURNS TABLE
  AS
    RETURN (
      SELECT 'Boston' as city
      UNION ALL 
      SELECT 'Barcelona' as city
      UNION ALL 
      SELECT 'Munich' as city
  )

PostgreSQL:

  CREATE OR REPLACE FUNCTION fn_get_cities()
    RETURNS TABLE (city TEXT)
  AS $$
  BEGIN
    RETURN QUERY (
      SELECT 'Boston' as city
      UNION ALL 
      SELECT 'Barcelona' as city
      UNION ALL 
      SELECT 'Munich' as city);
  END;
  $$ LANGUAGE plpgsql;

Note that PostgreSQL requires to specify the list of columns and their data types explicitly (after RETURNS TABLE keywords).

Both SQL Server and PostgreSQL table-valued functions can be called as follows:

  -- Call the table valued function
  SELECT * FROM fn_get_cities();

Query result:

In SQL Server, you can also specify the table name and column definitions, and then use one or multiple INSERT statements to insert data into the resulting table (multi-statement table-valued function):

SQL Server:

  CREATE FUNCTION fn_get_cities2()
  RETURNS @tab TABLE (city VARCHAR(30) NOT NULL, country VARCHAR(30))
  AS
  BEGIN
    INSERT INTO @tab SELECT 'Boston', 'United States' 
    INSERT INTO @tab SELECT 'Barcelona', 'Spain' 
 
    INSERT INTO @tab
      SELECT 'Berlin', 'Germany'
      UNION ALL 
      SELECT 'Warsaw', 'Poland'
    RETURN
  END    
  GO

In PostgreSQL, you can use RETURN QUERY statement to add rows into the resulting table. When a function uses multiple RETURN QUERY statements, their results are appended:

PostgreSQL:

  CREATE OR REPLACE FUNCTION fn_get_cities2()
  RETURNS TABLE (city TEXT, country TEXT)
  AS $$
  BEGIN
    RETURN QUERY SELECT 'Boston', 'United States'; 
    RETURN QUERY SELECT 'Barcelona', 'Spain'; 
 
    RETURN QUERY
      SELECT 'Berlin', 'Germany'
      UNION ALL 
      SELECT 'Warsaw', 'Poland';
    RETURN;
  END;    
  $$ LANGUAGE plpgsql;

Note that PostgreSQL does not allow specifying NOT NULL constraints for the columns in a table-valued function.

Using this function in SQL Server and PostgreSQL:

  -- Call the table valued function
  SELECT * FROM fn_get_cities2();

Query result:

There are some restrictions on how you can reference columns in PostgreSQL PL/pgSQL function that returns a table. Consider the following example:

PostgreSQL:

  CREATE OR REPLACE FUNCTION fn_get_cities3()
    RETURNS TABLE (city TEXT)
  AS $$
  BEGIN
    RETURN QUERY (
      SELECT city          -- Fails at runtime as the alias is required: SELECT a.city ...
      FROM 
      (
        SELECT 'Boston' as city
        UNION ALL 
        SELECT 'Barcelona' as city
        UNION ALL 
        SELECT 'Munich' as city
      ) a
   );
  END;
  $$ LANGUAGE plpgsql;

If you run this functions, you will get an error:

  SELECT fn_get_cities3();
 
  # ERROR:  column reference "city" is ambiguous
  # LINE 2:  SELECT city
  #                           ^
  # DETAIL:  It could refer to either a PL/pgSQL variable or a table column.

The reason is that defining RETURNS TABLE with a list of columns is equivalent to defining OUT parameters with the same names, that is why the name conflict happens when you refer to city column without the alias.

For more information, see SQL Server to PostgreSQL Migration.