CODEWITHSUNDEEP
functionpostgresqlcase
Mich Vellve
Mich Vellve

Reputation: 75

PostgreSQL CASE Function

I have tried to run this query:

CREATE Function sp_test_case () returns void as $$ 
DECLARE
    cont int=(Select MAX(id_fact)from backup_factura);
BEGIN

  while cont>0
  LOOP

    UPDATE backup_factura
    SET tipo= CASE 
            WHEN ((total_fact) <=100) THEN 'X'
            WHEN ((total_fact) <=200) THEN 'Y'
            ELSE 'Z'
           END;

    OUTPUT Deleted.tipo AS BeforeValue,
           Inserted.tipo AS AfterValue
    WHERE id_fact=cont;

    cont:=cont-1;

   END LOOP;

RETURN;   
END;   
$$ LANGUAGE plpgsql;

I adapted the code that was originally written in SQL server. Now my doubt is: Do you know the equivalent of the OUTPUT statement?

The OUTPUT clause is used to display the before and after vacation values. F.E:

OUTPUT Deleted.BusinessEntityID, Deleted.VacationHours AS BeforeValue, 
       Inserted.VacationHours AS AfterValue

Any suggestion of what do I need to do in order to make it work?

Thanks in advance for your time & support!

Upvotes: 0

Views: 856

Answers (1)

user330315
user330315

Reputation:

You can do all that without a function or a loop:

This will accomplish exactly the same thing:

UPDATE backup_factura 
    SET tipo = CASE 
                 WHEN total_fact <=100 THEN 'X'
                 WHEN total_fact <=200 THEN 'Y'
                 ELSE 'Z'
               END;

Strictly speaking you would need a where id_fact >= 0 to mimic your loop completely - but I'm guessing you don't have negative values in that column.

Note that the SQL case statement only needs end. There is no end case in SQL: http://www.postgresql.org/docs/9.3/static/functions-conditional.html

Unlike SQL Server, Postgres (and many other DBMS) makes a clear distinction between SQL (the query language) and procedural code (in your case PL/pgSQL). You can't mix the two languages (SQL Server only has a single language: T-SQL).

The case statement in PL/pgSQL indeed is terminated using end case: http://www.postgresql.org/docs/9.3/static/plpgsql-control-structures.html#AEN58362

Not sure what the output deleted is supposed to do as you are not deleting anything.

To return the new values from an update statement use the returning clause: http://www.postgresql.org/docs/current/static/sql-update.html

Upvotes: 2

Related Questions