[CALCITE-6308] Add PostgreSQL functions up to PostgreSQL v14

• Type: Improvement
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Fix Version/s: 1.38.0
• Component/s: core
• Assignee: Unassigned
• Reporter: James Duong
• Created: 07/Mar/24 21:28
• Updated: 17/Oct/24 22:41
• Resolved: 17/Oct/24 22:41

Description

Support the following PostgreSQL functions for parity with PostgreSQL 14:

• REGEXP_LIKE
  • The Spark version of this is being implemented in CALCITE-6278
  • The PostgreSQL version requires supporting a 3-arg version that takes in flags.
• REGEXP_REPLACE
  • There is an existing implementation.
  • PostgreSQL requires supporting an optional extra flags argument
• DATE_PART
  • PostgreSQL and Redshift let the date_part parameter be a string instead of a just an enum-like identifier (eg DATE_PART('year', ...) and DATE_PART(year, ...) are both supported.
  • SQL Server does not support using a string here.
• LOG
  • We have the BigQuery implementation which has a 1-arg overload that uses base e (ln), and a 2-arg overload which is LOG(value, base)
  • PostgreSQL's 1-arg version uses base 10 and the 2-arg overload has the order reversed – LOG(base, value)
• POW
  • Our existing implementation always returns double.
  • PostgreSQL allows returning a numeric instead of a double when inputs are numeric. Not sure if this makes sense though.
• RANDOM
  • This is an alias for RAND(), except it should not support passing in a seed.
• TO_CHAR
• TO_DATE
• TO_TIMESTAMP
  • PostgreSQL supports different format strings than the version we have implemented.

 

Sub-Tasks

• Add REGEXP_LIKE function (enabled in MySQL, Oracle, PostgreSQL and Spark libraries): Closed
• Add REGEXP_REPLACE function (enabled in PostgreSQL library): Closed
• Support Postgres DATE_PART function: Closed
• Add LOG function (enabled in PostgreSQL library): Closed
• Add POWER function for PostgreSQL: Closed
• Add RANDOM function (enabled in Postgres library): Closed
• Support PostgreSQL TO_DATE, TO_TIMESTAMP: Closed
• Support all PostgreSQL 14 date/time patterns for to_char: Closed
• Support all PostgreSQL 14 date/time patterns for to_date/to_timestamp: Closed
• Enable PostgreSQL implementations of to_date/to_timestamp: Closed