Search code examples
functionpostgresqltimestampexecute

How to use timestamp variable in EXECUTE format statement

I need an example of how to pass a timestamp in a PostgreSQL function and use that in an execute statement. I used an example like this:

CREATE OR REPLACE FUNCTION ref4(b1 timestamp) RETURNS refcursor AS $$   
DECLARE 
mycurs refcursor;

BEGIN 
OPEN mycurs FOR 
 EXECUTE format('SELECT * FROM  myTable WHERE  datetime <= '|| b1 ||'ORDER BY datetime' , b1);
RETURN mycurs;
END;$$ 
language plpgsql;

SELECT ref4( '2009/08/28 17:44:46.240' );

But it's not working due to the following error:

[Err] ERROR: syntax error at or near "17" LINE 1: ...T * FROM "mtTable" WHERE datetime <= 2009-08-28 17:44:46.2...

Solution

  • If you use format(), you should use placeholders in your format string (%s, %I, %L), like:

    SELECT format('SELECT * FROM  myTable WHERE datetime <= %L ORDER BY datetime' , b1);

    But: because you use EXECUTE, I suggest you to use the USING clause of it:

    EXECUTE 'SELECT * FROM  myTable WHERE datetime <= $1 ORDER BY datetime' USING b1;

    It's less error-prone, and you don't deal with text representations this way (format will convert its parameters into their text representation & execute will convert them back as appropriate, but this could result an unknown type too).

    Usually, they can be mixed too, but only if there are identifier(s), which comes from variable(s).

    Note, that if your code is this simple, there is no need for EXECUTE, you can use function parameters in sql statements within a function (as long as they don't collide with existing column names):

    SELECT * FROM  myTable WHERE datetime <= b1 ORDER BY datetime