why does this DO block have "syntax error near declare" in pgagent?

Question

why does this DO block have "syntax error near declare" in pgagent?

403 views Asked by Lautaro Aguilera At 26 May 2021 at 17:15

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

Original Q&A

There are 2 answers

**Pavel Stehule** · Answer 1 · 2021-05-27 04:03:45

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.

**AlexxxeyS** · Answer 2 · 2023-04-05 14:58:14

AlexxxeyS On 05 April 2023 at 14:58

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!

TechQA.

why does this DO block have "syntax error near declare" in pgagent?

There are 2 answers

Related Questions in POSTGRESQL

Related Questions in SYNTAX-ERROR

Related Questions in PLPGSQL

Related Questions in PGAGENT

Popular Questions

Popular Tags

Trending Questions