Skip to main content
Solved

Date functions only available in featureWriter transformer not in a Writer, why?

  • October 1, 2024
  • 5 replies
  • 97 views
so_much_more
Supporter
Forum|alt.badge.img+6

I need to write datetime to a comment on a SQL table after my postgres SQL writer is complete.

This can easily be done like when using a featureWriter like so:


COMMENT ON SCHEMA "my schema" IS '@DateTimeFormat(@DateTimeNow(), %Y-%m-%d %H:%M)';

This is not possible if i use a regular postgres writer. Only SQL functions are available. This alone is not enough to set a timestamp.
Tiny but frustrating neuance between almost identical transformer/writers. What other ways can i inject a datetime into my comment on schema syntax?

 

 

Best answer by bwn

Hmmm, been years since had a PostGreSQL instance to design on.

Alright well this is a hack to create a dynamic string literal using FORMAT() and give a value formatted to a string literal with the %L format flag.

 

FME_SQL_DELIMITER ;

DO $$                      
BEGIN
    EXECUTE FORMAT('COMMENT ON SCHEMA “my schema” IS %L', TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH:MM'));
END $$ LANGUAGE PLPGSQL;

 

 

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

5 replies

hkingsbury
Celebrity
Forum|alt.badge.img+65
  • hkingsburyCelebrity
  • Celebrity
  • October 1, 2024

@ctredinnick will be able to help with this :)

bwn
Evangelist
Forum|alt.badge.img+26
  • bwnEvangelist
  • Evangelist
  • October 2, 2024

Why not use instead:

COMMENT ON SCHEMA "my schema" IS LOCALTIMESTAMP(0); 
Or if really want the current datetime truncated to current minute
COMMENT ON SCHEMA "my schema" IS DATE_TRUNC(‘minute’,LOCALTIMESTAMP());

 

so_much_more
Supporter
Forum|alt.badge.img+6
  • so_much_moreSupporter
  • Author
  • Supporter
  • October 2, 2024

Why not use instead:

COMMENT ON SCHEMA "my schema" IS LOCALTIMESTAMP(0); 
Or if really want the current datetime truncated to current minute
COMMENT ON SCHEMA "my schema" IS DATE_TRUNC(‘minute’,LOCALTIMESTAMP());

 

I tried both examples (and other similar examples)
postgres throw an error using anything other than pure strings when setting commets. Thats why i need to use FME functions to inject the string directly into the SQL.

 syntax error at or near "DATE_TRUNC"

 

bwn
Evangelist
Forum|alt.badge.img+26
  • bwnEvangelist
  • Evangelist
  • Best Answer
  • October 2, 2024

Hmmm, been years since had a PostGreSQL instance to design on.

Alright well this is a hack to create a dynamic string literal using FORMAT() and give a value formatted to a string literal with the %L format flag.

 

FME_SQL_DELIMITER ;

DO $$                      
BEGIN
    EXECUTE FORMAT('COMMENT ON SCHEMA “my schema” IS %L', TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH:MM'));
END $$ LANGUAGE PLPGSQL;

 

 

nielsgerrits
so_much_more
so_much_more
Supporter
Forum|alt.badge.img+6
  • so_much_moreSupporter
  • Author
  • Supporter
  • October 8, 2024

Hmmm, been years since had a PostGreSQL instance to design on.

Alright well this is a hack to create a dynamic string literal using FORMAT() and give a value formatted to a string literal with the %L format flag.

 

FME_SQL_DELIMITER ;

DO $$                      
BEGIN
    EXECUTE FORMAT('COMMENT ON SCHEMA “my schema” IS %L', TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH:MM'));
END $$ LANGUAGE PLPGSQL;

 

 

Thanks that worked! Thank you for the help :)

Community Stats

32,154
Posts
122,043
Replies
39,812
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.