4

I was under the impression that argument-less functions can be called with empty parentheses after the function name, i.e. what some other databases allow to do:

current_timestamp()

Whereas in Oracle, I have to write

current_timestamp

With user-defined functions, this rule doesn't apply (in 11g). I can write both

my_function
my_function()

My question is: Is CURRENT_TIMESTAMP really a true function or should I consider it to be a language construct / pseudo-column of the Oracle SQL dialect (compatible with the SQL standard)? Is there any formal definition about when I can (optionally, mandatorily) add the () and when I have to omit them?

Background-info:

  • SQL 1992 defines:

    <current timestamp value function> ::=
  CURRENT_TIMESTAMP [ <left paren> <timestamp precision> <right paren> ]

  • Derby, HSQLDB, Ingres, Postgres, SQLite, SQL Server behave like Oracle, where there are no parentheses allowed for CURRENT_TIMESTAMP

  • Sybase SQL Anywhere knows a CURRENT TIMESTAMP function (without parentheses, without underscore)
  • CUBRID, MySQL, Sybase ASE allow for using CURRENT_TIMESTAMP()
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509

1 Answers1

7

SQL standards back to 1992 refer to CURRENT_TIMESTAMP as both a "time-varying system variable" and a "datetime value function". See, for example, Database Language SQL.

But AFAIK the standards always use CURRENT_TIMESTAMP, never CURRENT_TIMESTAMP(). Using CURRENT_TIMESTAMP() on a compliant dbms should fail with a syntax error.

I'm not sure what the standards have to say about user-defined functions.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185