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.

Answers


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';

Need Your Help

Php curl error : Hostname was NOT found in DNS cache

php apache curl dns bind9

I have a problem with a curl request in my php script that i have uploaded on my dedicated webserver. I know that this curl request work fine, because i have already tested on my local machine. I t...