Postgres Anonymous Code Block Syntax Error

Question

I'm having a hard time here with pl-pgsql syntax.

here's the code:

DO $$
DECLARE 
dia_semana INT := CAST(EXTRACT(DOW FROM CURRENT_DATE)as INT);
dia INT :=  CASE WHEN dia_semana = 0 THEN dia := 7;
         WHEN dia_semana = 1 THEN dia := 6;
         WHEN dia_semana = 2 THEN dia := 5;
         WHEN dia_semana = 3 THEN dia := 4;
         WHEN dia_semana = 4 THEN dia := 3;
         WHEN dia_semana = 5 THEN dia := 2;
         WHEN dia_semana = 6 THEN dia := 1;
BEGIN   
COPY(SELECT CURRENT_DATE + dia)
TO '/tmp/dump.sql';
END $$;

raises the error:

'LINE 4: dia INT := CASE WHEN dia_semana = 0 THEN dia := 7;'

with cursor on ":" "dia:= 7". Already tried a normal assignment "dia = 7" without success. Can someone please lend me a hand here?

tnx.

Clodoaldo Neto · Accepted Answer · 2014-02-11 16:00:53Z

2

There is no semicolon ; between the cases neither the assignment inside each case. This case syntax is better here

dia int := case dia_semana

    when 0 then 7
    when 1 then 6
    when 2 then 5
    when 3 then 4
    when 4 then 3
    when 5 then 2
    when 6 then 1
    end;

But you can simple do

dia int := 7 - dia_semana;

answered Feb 11, 2014 at 16:00

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

XVirtusX Over a year ago

Hi Clodoaldo, tnx for the help got that "case" issue handled now. But the code still doesn't work. PGSQL is treating my variable as a column... heres the message: "ERROR: column "dia" don't exist LINE 1: COPY(SELECT CURRENT_DATE + dia)"

mu is too short Over a year ago

@XVirtusX: I think you'll need to build your COPY as a string and EXECUTE it: stackoverflow.com/a/19970705/479863

Clodoaldo Neto Over a year ago

@XVirtusX I can't reproduce the error. Show the modified code

XVirtusX Over a year ago

@muistooshort How should I concatenate the variable with the string? I tried the following... didnt work out: "execute 'COPY(SELECT CURRENT_DATE + ' || dia ||')' || 'TO /home/tmp/dump.sql';

Clodoaldo Neto Over a year ago

@XVirtusX That error does not happen to me in 9.3. What is your version?

|

Collectives™ on Stack Overflow

Postgres Anonymous Code Block Syntax Error

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related