How to properly insert Timestamp using plpgsql and EXECUTE format...USING dynamic SQL

Question

I have a dynamic SQL statement I'm building up that is something like

my_interval_var := interval - '60 days';

sql_query := format($f$ AND NOT EXISTS (SELECT 1 FROM some_table st WHERE st.%s = %s.id AND st.expired_date >= %L )$f$, var1, var2, now() - my_interval_var)

1. Regarding my first question, it seemed to insert the Timestamp correctly (it seems) after the now() - my_interval_var computation. However, I just want to make sure I don't need to cast anything or something, because the only way I could get it work was if I used %L, which is the string literal Identifer. Or does postgres allow direct comparisons with Strings that represent Time without a cast?, like

some_column <= '2021-12-31 00:00:00'; // is ::timestamp cast needed?

2. Second of all, regarding the sql_query variable that I concatenated an SQL String into above, I actually wanted to skip the Format I did, and directly inject this sql_query variable into an EXECUTE...FORMAT...USING statement.

I couldn't get it to work, but something like this:

EXECUTE format($f$ SELECT * 
FROM %I tbl_alias 
WHERE tbl_alias.%s = %L
%s ) USING var1, var2, var3, sql_query;

Is it possible to leave the Dynamic SQL Identifiers %I %L and %s inside the variable and FORMAT it at the EXECUTE... level? Something tells me this isn't possible, but it would be really cool.

3. Also last question I didn't want to add, but I feel someone might have a quick answer. I was using the ]

FOR temprecord IN
   SELECT myCol1, myCol2, myCol3 
   FROM  %I tbl',var1)
   LOOP
    EXECUTE temprecord.someColumnOnMyTbl;
   END LOOP;

...but I could not for the life of get the EXECUTE temprecord.someColumnOnMyTbl statement to work when I made the query dynamic. I tried everything identifier, using FORMAT, USING...

I thought columns were strings like %s because I do that for columns all the time when they are aliased like alias.%s = 'some string literal'

ANyway, I couldn't get it to work, I wanted to make the column name dynamic but tried all these things

EXECUTE format($f$ %I.%s $f$, var1, var2);

EXECUTE format($f$ %$1.%$2 $f$) USING var1, var2;


EXECUTE format($f$ %I.someColumnOn%s $f$, var1, var2);


EXECUTE format($f$ $1.someColumnOn$2 $f$) USING var1, var2;

Anyway, I tried more stuff than that, but I actually got some data from the DB when I made the temprecord variable an %I but I am Selecting 3 columns and it looked like sommething got jacked up with the second identifier because I got a syntax error and it looked like it was trying to concatenate all 3 columns of the query results...

I did try hardcoding it and that worked fine... any help appreciated!

Answer 1

String literal is unknown type value. Postgres always does cast to some target binary format. The type is deduced from context. When you use function format, and %L placeholder, then any binary value is converted to string, and escaped to Postgres's string literal (protection against syntax errors, and SQL injection). When you use USING clause, then the binary value is passed directly to executor. It is little bit faster, and there is not possibility to lost some information under cast to string. Without these points, the real effect of %L and USING clause is almost same.

Your type of variable is timestamp. Probably type of expired_date column is date type. So some conversion timestamp->date is necessary.

Function format is just string function. It just make string. For better readability it supports placeholders, that ensure correct escaping and correct result SQL string. %L is same like calling function quote_literal and %I is same like quote_ident (for column, table names). %s inserts string without escaping and quoting. The result of format function (when you use it in EXECUTE command) should be valid SQL statement. You can use it in RAISE NOTICE command, and you can print result to debug output. Usually it is good idea

DECLARE
  query text;
  x date DEFAULT current_date
  y int;
BEGIN
  query := format('.... WHERE inserted = $1', ...);
  RAISE NOTICE 'dynamic query will be: %', query);
  EXECUTE query USING x INTO y;
  ...

Clause USING allows using parameters in dynamic SQL (EXECUTE clause). Usually, the format's placeholdres should be used for table or column names, and USING for any other.

For types date and timestamp (scalars basic types) the following execution will be on 99.99% same:

EXECUTE format('select count(*) from foo where inserted = %L', current_date) INTO ..

EXECUTE 'select count(*) from foo where inserted = $1' USING current_date INTO ..

You cannot to use query parameters on column name or table name positions. This is limit of USING clause. But for any other cases, this clause should be used primary.