On Mon, Jun 8, 2020 at 2:57 PM Alistair Johnson <aewj@mit.edu> wrote:
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?
I think you need to be more specific as to what "this" means.
Looking again after Andrian's comment are you trying to write, in the script file:
datetime_diff('start time as string'::timestamp, 'end time as string'::timestamp, HOUR)
and get PostgreSQL to recognize the value HOUR as a custom type value without single quotes surrounding it
If that is the question the answer is no. The only type literals that can be written without single quotes are numbers.
The parsing of SQL can handle some standard mandated non-quoted constants but they are basically keywords, not values.
