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

On Mon, Jun 8, 2020 at 2:57 PM Alistair Johnson <aewj(at)mit(dot)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.

David J.