0

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.

XVirtusX
  • 679
  • 3
  • 11
  • 30

1 Answers1

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;
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • 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)" – XVirtusX Feb 11 '14 at 16:12
  • @XVirtusX: I think you'll need to build your COPY as a string and EXECUTE it: http://stackoverflow.com/a/19970705/479863 – mu is too short Feb 11 '14 at 16:16
  • @XVirtusX I can't reproduce the error. Show the modified code – Clodoaldo Neto Feb 11 '14 at 16:19
  • @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'; – XVirtusX Feb 11 '14 at 16:49
  • @XVirtusX That error does not happen to me in 9.3. What is your version? – Clodoaldo Neto Feb 11 '14 at 16:59
  • @ClodoaldoNeto 9.1. It's really an odd error. If I substitute my var for an integer COPY(SELECT CURRENT_DATE + 1) it works fine. – XVirtusX Feb 11 '14 at 17:07
  • @XVirtusX: This should be easy to solve. Post a new question with complete information (verbatim code and error message). – Erwin Brandstetter Feb 12 '14 at 13:50