Functions and Operators (PostgreSQL)

Set Returning Functions

generate_series

  SELECT * FROM generate_series(2,4);
  SELECT generate_series('2025-01-01'::date, '2025-01-31'::date, '2 day')::date as date;

jsonb_to_recordset

  SELECT *
  FROM jsonb_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]'::jsonb)
  as x(a int, b text);

string_to_table

  SELECT string_to_table('a,b,c,d,e,f', ',') as letter;

unnest

  SELECT unnest(ARRAY[1,2,3]) AS seq;
  -- or add ordinality
  SELECT *
  FROM unnest(ARRAY['a', 'b', 'c'])
  WITH ORDINALITY AS t(item, order);

FROM ROWS

Polymorphic SQL functions

PL/pgSQL functions

CREATE OR REPLACE FUNCTION
  function_name(arg1 INT, arg2 INT, arg3 INT)
RETURNS
  SETOF VARCHAR
LANGUAGE
  plpgsql
AS
$$
BEGIN
  (...)
END;
$$;

References:

Backlinks: