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:
Boston |
Barcelona |
Munich |
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.