Is it possible to use keywords (date units) in a function definition?

From: Martin Gainty <mgainty(at)hotmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Is it possible to use keywords (date units) in a function definition?
Date: 2020-06-09 00:06:42
Message-ID: BLAPR19MB4227A67A5EC06EEEEC6B10BDAE820@BLAPR19MB4227.namprd19.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I recently tried to write a wrapper function to calculate the difference between two dates, mainly as a convenience. I'd essentially be emulating EXTRACT(<dateunit> FROM date1 - date2), in various ways. I got a bit stuck on allowing specification of the <dateunit>: is this possible in function definitions? I'd like to be able to write something along the lines of:

CREATE OR REPLACE FUNCTION DATETIME_DIFF(end TIMESTAMP(3), start TIMESTAMP(3), datepart UNIT) RETURNS DOUBLE PRECISION AS $$
BEGIN
RETURN EXTRACT(datepart FROM end - start);
END; $$
LANGUAGE PLPGSQL;

One option would be to treat datepart as a string, but this doesn't work for my use case. (Background: I'm trying to refactor a bunch of SQL scripts to work on Google BigQuery and PostgreSQL by writing PostgreSQL functions to emulate BigQuery functions. Unfortunately BigQuery does not recognize the third argument if it is a string (i.e. 'HOUR' does not work but HOUR does)).

Any ideas? Is this even possible?

-Alistair

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2020-06-09 00:16:44 Re: Is it possible to use keywords (date units) in a function definition?
Previous Message Peter 2020-06-09 00:02:53 12.2: Why do my Redo Logs disappear??