Skip to main content
Solved

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

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;

 

 

View original
Did this help you find an answer to your question?

5 replies

hkingsbury
Celebrity
Forum|alt.badge.img+50
  • 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
bwn wrote:

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
2 people upvoted this
  • nielsgerrits
    nielsgerrits
  • so_much_more
    so_much_more
so_much_more
Supporter
Forum|alt.badge.img+6
  • so_much_moreSupporter
  • Author
  • Supporter
  • October 8, 2024
bwn wrote:

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 :)

Reply


Most helpful members this week

Community Stats

30,846
Posts
116,941
Replies
38,670
Members
Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
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.