Can my DB?

CREATE FUNCTION get_recent_orders(cutoff_date DATE)
RETURNS TABLE(order_id INT, customer TEXT, total NUMERIC)
LANGUAGE sql
STABLE
AS $$
  SELECT id, customer_name, total
  FROM orders
  WHERE created_at >= cutoff_date;
$$;

SELECT * FROM get_recent_orders('2024-01-01');

• RETURNS TABLE(col type, ...) defines the output row type inline.
• RETURNS SETOF record or a specific composite type also works.
• Can be called directly in FROM clause: SELECT * FROM my_function(arg).
• PL/pgSQL functions use RETURN NEXT or RETURN QUERY to emit rows.

• MySQL functions can only return scalar values.
• No table-valued function support exists.
• Use stored procedures with result sets or views as workarounds.
• Lateral derived tables or CTEs can partially compensate for some TVF use cases.

• Same limitation as MySQL - no table-valued functions.
• Functions return only scalar values.
• Sequence virtual tables (seq_0_to_n) are built-in TVF-like objects but not user-defined.

-- Step 1: Define row and table types
CREATE TYPE order_row AS OBJECT (
  order_id NUMBER, customer VARCHAR2(100), total NUMBER
);
CREATE TYPE order_table AS TABLE OF order_row;

-- Step 2: Create pipelined function
CREATE FUNCTION get_recent_orders(cutoff_date DATE)
RETURN order_table PIPELINED AS
BEGIN
  FOR r IN (SELECT id, customer_name, total FROM orders
            WHERE created_at >= cutoff_date) LOOP
    PIPE ROW(order_row(r.id, r.customer_name, r.total));
  END LOOP;
END;
/

SELECT * FROM TABLE(get_recent_orders(DATE '2024-01-01'));

• Requires creating custom nested table types before defining the function.
• PIPELINED keyword enables streaming row-by-row return to the caller.
• Must be called within TABLE() constructor in the FROM clause.
• More complex setup than PostgreSQL or SQL Server TVFs but still native table-valued function support.

-- Inline TVF (preferred, optimizer can inline it)
CREATE FUNCTION dbo.get_recent_orders(@cutoff_date DATE)
RETURNS TABLE
AS RETURN (
  SELECT id AS order_id, customer_name AS customer, total
  FROM orders
  WHERE created_at >= @cutoff_date
);

SELECT * FROM dbo.get_recent_orders('2024-01-01');

-- Multi-statement TVF
CREATE FUNCTION dbo.get_orders_summary(@cutoff_date DATE)
RETURNS @result TABLE (customer TEXT, order_count INT)
AS BEGIN
  INSERT @result
  SELECT customer_name, COUNT(*)
  FROM orders WHERE created_at >= @cutoff_date
  GROUP BY customer_name;
  RETURN;
END;

• Inline TVFs contain a single RETURN (SELECT ...) and are treated as parameterized views by the optimizer.
• Multi-statement TVFs use an explicit @table variable and support complex procedural logic.
• Inline TVFs are significantly more efficient than multi-statement TVFs due to optimizer inlining.
• Both types are called in the FROM clause like a table.

-- Built-in TVF example
SELECT key, value
FROM json_each('[{"a":1},{"a":2}]');

-- Custom TVF via C API (conceptual)
-- sqlite3_create_window_function() and eponymous virtual tables
-- are the mechanism for custom TVFs.

• No CREATE FUNCTION for TVFs exists in SQL syntax.
• Built-in table-valued functions: json_each(), json_tree(), generate_series() (some builds).
• Custom TVFs can be implemented in C using the eponymous virtual table mechanism.
• Language bindings (Python, Node.js) do not expose TVF registration as easily as scalar functions.

-- Built-in SPLIT_TO_TABLE TVF
SELECT index, value
FROM SPLIT_TO_TABLE('a,b,c', ',');

• User-defined table-valued functions are not supported.
• Some TVF capabilities via Python UDFs that return multiple values, but not true table output.
• Built-in table functions like SPLIT_TO_TABLE are available.
• Use subqueries, CTEs, or external tools for table-generating patterns.

CREATE FUNCTION get_recent_orders(cutoff_date DATE)
RETURNS TABLE(order_id INT, customer VARCHAR(100), total DECIMAL(10,2))
LANGUAGE SQL
READS SQL DATA
BEGIN
  RETURN SELECT id, customer_name, total
         FROM orders
         WHERE created_at >= cutoff_date;
END;

SELECT * FROM TABLE(get_recent_orders('2024-01-01')) AS t;

• RETURNS TABLE(...) syntax defines the output columns inline.
• Must be called using TABLE() constructor in the FROM clause.
• Also supports external table functions implemented in Java or C.
• READS SQL DATA, MODIFIES SQL DATA, or NO SQL declares data access level.

-- SQL table UDF
CREATE FUNCTION get_recent_orders(cutoff_date DATE)
RETURNS TABLE(order_id NUMBER, customer TEXT, total FLOAT)
AS $$
  SELECT id, customer_name, total
  FROM orders
  WHERE created_at >= cutoff_date
$$;

SELECT * FROM TABLE(get_recent_orders('2024-01-01'::DATE));

• Table UDFs (UDTFs) support SQL, JavaScript, and Python (Snowpark).
• Must be called using TABLE() constructor in the FROM clause.
• Python UDTFs can yield rows one at a time using a generator pattern.
• Snowpark Python table functions support partition-by processing for scalable patterns.

CREATE TABLE FUNCTION my_dataset.get_recent_orders(cutoff_date DATE)
AS (
  SELECT id AS order_id, customer_name AS customer, total
  FROM my_dataset.orders
  WHERE DATE(created_at) >= cutoff_date
);

SELECT * FROM my_dataset.get_recent_orders('2024-01-01');

• TVFs added in 2022 using CREATE TABLE FUNCTION syntax.
• Called directly in FROM clause without a TABLE() wrapper.
• Supports parameterized table functions for dynamic filtering.
• TVFs can reference other tables, views, and UDFs.