From: | Alistair Johnson <aewj(at)mit(dot)edu> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Is it possible to use keywords (date units) in a function definition? |
Date: | 2020-06-08 21:21:43 |
Message-ID: | CAKHmqNC0utZwF1GWgQD4+cxa+VwNQHLKuo9Cmx4Mh4khYQZYxg@mail.gmail.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
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2020-06-08 21:25:12 | Re: When to use PARTITION BY HASH? |
Previous Message | Michael Lewis | 2020-06-08 21:15:15 | Re: Index no longer being used, destroying and recreating it restores use. |