1

I have the following code:

DROP TABLE IF EXISTS pltest;
CREATE TABLE pltest (x jsonb);

DO $$
DECLARE startdate text := to_char(current_date - 1, 'YYYYMMDD');
BEGIN 

EXECUTE format(
           'COPY pltest FROM PROGRAM ''curl "https://example.com/events/start_date=%sT000000Z"''',
           startdate
        );
        
END
$$ ;

It basically imports the URL with a parameterized COPY statement so it always imports the data of the last 24 hours, it runs perfectly in SQL shell, but when i tried adding a pgagent job with that code, pgagent returns "syntax error near DECLARE".

PostgreSQL version: 13.3

PgAgent version: 13

Lautaro Aguilera
  • 53
  • 5
  • I'm not familiar with pgagent but that client might not recognize dollar quoting or anonymous functions (DO block). You may be able to get around that by specifying the language for the anonymous block. For example, `END $$ LANGUAGE PLPGSQL;` – J Spratt May 26 '21 at 18:44
  • Please, always declare your version of Postgres (and pgagent in this case) in questions. – Erwin Brandstetter May 29 '21 at 14:53
  • Sorry for that, i edited the post and added them. – Lautaro Aguilera May 30 '21 at 18:26

2 Answers2

0

This code looks correct. It fails with expected error.

ERROR:  invalid input syntax for type json
DETAIL:  Token "<" is invalid.
CONTEXT:  JSON data, line 1: <...
COPY pltest, line 1, column x: "<!doctype html>"
SQL statement "COPY pltest FROM PROGRAM 'curl "https://example.com/events /start_date=20210526T000000Z"'"
PL/pgSQL function inline_code_block line 5 at EXECUTE

Maybe your client breaks source code, or maybe your Postgres is too old.

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
0

Try to remove double dollar symbol before you push a save button. Then, after step was saved successfully, edit it and bring double dollars back and save changes. Good luck!

AlexxxeyS
  • 9
  • 2