0

I have been trying to simulate the following Oracle statement in PostgreSQL:

SELECT TRUNC(SYSDATE)

To reach this, I was already able to simulate the TRUNC() function receiving only one time datatype parameter, which is timestamp without time zone. This is how I made it:

CREATE OR REPLACE FUNCTION public.trunc(teste TIMESTAMP WITHOUT TIME ZONE)
RETURNS TIMESTAMP WITHOUT TIME ZONE
LANGUAGE plpgsql
AS 
$BODY$
BEGIN
  RETURN DATE_TRUNC('DAY', teste);
END
$BODY$
language 'plpgsql';

It works just fine when I try to call this function passing the CLOCK_TIMESTAMP() function as an argument but casting it at the same time. Just like this:

SELECT TRUNC(CLOCK_TIMESTAMP()::TIMESTAMP WITHOUT TIME ZONE);

So that I can get around this whole statement, I'd like to make it more practical replacing this entire argument for SYSDATE. I've already tried to create the same SYSDATE function in PostgreSQL:

CREATE OR REPLACE FUNCTION public.sysdate()
RETURNS TIMESTAMP WITHOUT TIME ZONE
AS
$BODY$
BEGIN
  RETURN DATE_TRUNC('SECOND', CLOCK_TIMESTAMP() AT TIME ZONE '<<My_timezone>>');
END
$BODY$
language plpgsql;

Well... I'm not gonna say it does not work, because it does. Nevertheless, everytime I call the TRUNC() function created by me, I need to pass SYSDATE as an argument using parentheses:

SELECT TRUNC(SYSDATE());

That is, although it is similar from what I am trying to simulate, it isn't syntactically correct. Would there be a way to take off the parentheses from it while calling SYSDATE function?

Futhermore, is there any observation to be done about what I am trying to do? It's important to quote that the timestamp without time zone was chosen by purpose, although SYSDATE returns DATE type by default.

5
  • 4
    Can't you use select current_date; ? See dbfiddle.uk/… Commented Aug 1, 2022 at 16:12
  • @TheImpaler, I can't because the datetype chosen to be used was the timestamp without time zone by default. That's why those functions and casting were made as being of this type, moreover, the central idea is to use the keyword SYSDATE. Commented Aug 1, 2022 at 16:31
  • 2
    If you are looking for something that works in Oracle and Postgres, then using current_date would achieve that. Commented Aug 1, 2022 at 16:42
  • No need for plpgsql, a simple SQL function can do the trick Commented Aug 1, 2022 at 17:15
  • @FrankHeikens: but that still doesn't allow to create a function where you can leave out the parentheses Commented Aug 1, 2022 at 18:08

1 Answer 1

2

You cannot to create function in Postgres, that can be called without parenthesis. This is not supported. Every call of function have to use parenthesis.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.