Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Alistair Johnson
Date:
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

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

From
"David G. Johnston"
Date:
On Mon, Jun 8, 2020 at 2:57 PM Alistair Johnson <aewj@mit.edu> wrote:

RETURN EXTRACT(datepart FROM end - start);
Any ideas? Is this even possible?

Use the "date_part" function.

David J.

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

From
Adrian Klaver
Date:
On 6/8/20 3:10 PM, David G. Johnston wrote:
> On Mon, Jun 8, 2020 at 2:57 PM Alistair Johnson <aewj@mit.edu 
> <mailto:aewj@mit.edu>> wrote:
> 
> 
>     RETURN EXTRACT(datepart FROM end - start);
>     Any ideas? Is this even possible?
> 
> 
> Use the "date_part" function.

That would work on the Postgres side, but the OP also wants this to run 
on BigQuery and unfortunately it won't work there.

> 
> David J.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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

From
"David G. Johnston"
Date:
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.

David J.

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

From
Alistair Johnson
Date:
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.

Thanks David - that was exactly my question - and it's nice to have a clear answer even if it wrinkles my plans a bit!

-Alistair