PostgreSQL CASE Function

Question

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!

There is no OUTPUT Deleted.tipo AS BeforeValue option in Postgres. Are you looking for the returning clause? postgresql.org/docs/current/static/sql-update.html You also didn't declare the Beforevalue and AfterValue variables. And you are not using them anyways. What exactly are you trying to achieve? As far as I can tell, you can simply use: UPDATE backup_factura SET tipo = CASE WHEN total_fact <=100 THEN 'X' WHEN total_fact <=200 THEN 'Y' ELSE 'Z' END CASE; no need for the loop no need for the where clause. That will be much faster. — user330315
– user330315, Commented Oct 7, 2014 at 20:07
The error in fact is not there (according to PostgreSQL is in other part). Check my edit — Mich Vellve
– Mich Vellve, Commented Oct 7, 2014 at 20:11
That doesn't matter, there is no output clause in Postgres. Check the manual (you are also ending the update statement with an ; then you have the (invalid) output clause with a where clause that doesn't make sense. — user330315
– user330315, Commented Oct 7, 2014 at 20:12

score 2 · Accepted Answer · 2014-10-07 20:27:17Z

2

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

edited Oct 7, 2014 at 20:27

answered Oct 7, 2014 at 20:15

user330315

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

6 Comments

Mich Vellve Over a year ago

Thanks for your answer. I looked for the output and it is a statement from SQL Server. The OUTPUT clause is used to display the before and after a variable value.

user330315 Over a year ago

You should use the Postgres manual, not the SQL Server manual.

Mich Vellve Over a year ago

Haha I know, I did edit my post. I am trying to change from SQL Server to this PostgreSQL DBMS

user330315 Over a year ago

You can use the returning clause to return the updated values. I don't think you can get the "old value" from an update statement.

Mich Vellve Over a year ago

Could you teach me in your post how to return the updates values please? I think I will do it that way!

|

Collectives™ on Stack Overflow

PostgreSQL CASE Function

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related