While starting up my app, I need to do a sanity check within my database. In a table sensor, there's a column seconds. Now the function should take an argument id.

It then should simply do something like:

select STARTTIME,SECONDS from sensors where SECONDS = 0;

The rows are not allowed to be 0, which only could happen if the app crashed. So if this function finds some rows, it needs to repair them by checking another table:

select "timestamp" from secure_sanity where id = *id*;

and then it should calculate STARTTIME - TIMESTAMP (from secure_sanity) and set this value to seconds.

I have no idea how to do this as I am completely new to PL/pgSQL. I would rather do this from within the app, but it feels like it would be much more efficient to do this within a function.

Could you give me a clue?

  • 'The rows are not allow to be 0' - what exactly cannot be 0? or NULL, maybe? – dezso Feb 3 '16 at 10:48

It seems you just need the query:

UPDATE sensors 
 SET seconds=(SELECT sensors.starttime-"timestamp" FROM secure_sanity
              WHERE id=*id*)
   WHERE seconds=0;

For that, it's overkill to write a plpgsql function.

Or if you really want one, just replace this *id* by a function parameter put the above UPDATE query inside a CREATE FUNCTION / BEGIN / END block.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.