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:

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_cities2()
    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_cities2();
 
  # 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.