SELECT FROM Function - SQL Server to PostgreSQL Migration

In SQL Server and PostgreSQL, you can query a table-valued function in the FROM clause of the SELECT statement.

SQL Server:

  -- A table-valued function
  CREATE FUNCTION getColors()
  RETURNS TABLE
  AS
  RETURN (
    SELECT 'Red' AS name, 'R' AS category
    UNION ALL
    SELECT 'Blue' AS name, 'B' AS category
    UNION ALL
    SELECT 'Green' AS name, 'G' AS category
  )
  GO
 
  -- Use the function in FROM
  SELECT * FROM getColors();
 
  -- Using alias with AS keyword
  SELECT t.name, t.category FROM getColors() AS t;
 
  -- Using alias 
  SELECT t.name, t.category FROM getColors() t;

PostgreSQL:

  -- A table-valued function
  CREATE OR REPLACE FUNCTION getColors()
    RETURNS TABLE (name TEXT, category TEXT)
  AS $$
  BEGIN
    RETURN QUERY (
      SELECT 'Red' AS name, 'R' AS category
      UNION ALL
      SELECT 'Blue' AS name, 'B' AS category
      UNION ALL
      SELECT 'Green' AS name, 'G' AS category
    );
  END;
  $$ LANGUAGE plpgsql;
 
  -- Use the function in FROM
  SELECT * FROM getColors();
 
  -- Using alias with AS keyword
  SELECT t.name, t.category FROM getColors() AS t;
 
  -- Using alias 
  SELECT t.name, t.category FROM getColors() t;

For more information, see SQL Server to PostgreSQL Migration.