How to use ' in literal String in PostgreSQL
I have learned that JPA- Hibernate does not support INTERVAL keyword. To deal with that problem I tried writing a function which in turn make use of INTERVAL keyword. All I need to do is to call this function from Hibernate.
Below is the function I want to write. It is throwing syntax error.
CREATE OR REPLACE FUNCTION getTimeStampAfterDeductingHours(abc varchar) returns TIMESTAMPTZ as $$ select CURRENT_TIMESTAMP - INTERVAL ''' ' || abc || 'hour '; $$ language 'sql';
ERROR: invalid input syntax for type interval: "' "
Basically I want to use the input parameter in the SQL statement. I have tried many ways to include it. But could not do it.
The error is:
ERROR: invalid input syntax for type interval: "' " LINE 3: select CURRENT_TIMESTAMP - INTERVAL ''' ' || abc || 'hour ... ^
That's because you can only use the INTERVAL 'some-interval-here' pattern for interval literals. PostgreSQL binds the INTERVAL tighter than the || operator, so you're doing:
(INTERVAL ''' ') || ...
which makes no sense.
In this case you must construct a string then cast it to INTERVAL.
select CURRENT_TIMESTAMP - CAST(''' ' || abc || 'hour ' AS interval);
I have no idea what you're trying to achieve with the leading ', and without seeing the input you send to the function I can't really guess.
Guessing by the name of the function, you actually want to subtract the hours passed as an argument from the current timestamp. If so, you should pass an integer, not a string, and multiply the interval:
CREATE OR REPLACE FUNCTION getTimeStampAfterDeductingHours(hours integer) returns TIMESTAMPTZ as $$ SELECT current_timestamp - (hours * INTERVAL '1' HOUR); $$ language 'sql';