How do you use SELECT...FOR UPDATE in a function?

Question

Going off of this example, I am trying to lock someone's credit card account (row), check to see if they have enough money, and if they do, use it to pay. I need to lock it to prevent a condition where they have enough credit, but then it is used on another transaction and my program is left thinking it still does have enough credits.

At the terminal level I can accomplish this like so: I can open two psql sessions in two terminals and I can issue a SELECT * FROM credit_card WHERE credit_card_number = 1234 FOR UPDATE; command in one and then a SELECT * FROM credit_card FOR UDPATE in the other (or something else like UPDATE credit_card SET credits = credits -99 WHERE credit_card_number = 1234), and I can see that the former call blocks the latter call. However, when I do the same thing but in a function like so

CREATE OR REPLACE FUNCTION foo (p_credit_card_number BIGINT)
RETURNS VOID
AS $$
BEGIN
    SELECT * FROM credit_card WHERE credit_card_number = p_credit_card_number FOR UPDATE
    ...
END
$$
LANGUAGE 'plpgsql';

I get the typical query has no destination for result data error (eg. see here).

Question: How can I lock a specific row, or number of rows, while using SELECT ...FOR UPDATE inside a Function while avoiding the above mentioned error?? If not possible, or not advisable, how else would I do this inside a Function?

Use `PERORM`. See [Perform](https://www.postgresql.org/docs/13/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT). — Adrian Klaver, Oct 30 '20 at 19:16
@AdrianKlaver, `PERFORM * FROM credit_card WHERE credit_card_number = p_credit_card_number FOR UPDATE`? — puk, Oct 30 '20 at 19:57
Yes per the docs in the link I sent: "Write the query the same way you would write an SQL SELECT command, but replace the initial keyword SELECT with PERFORM." — Adrian Klaver, Oct 30 '20 at 20:01

score 1 · Accepted Answer · answered Oct 31 '20 at 23:43

The error you are getting has nothing to do whit the "for update" clause. It works exactly the same in a stored procedure of not. The error you has to to do this a select statement inside a procedure (or function or do block). When you select in a procedure you must tell the procedure what to do with the selected variable(s). This you do with the Select <column_list> into <variable_list> ...
So assuming you actually need the data then something like the following:

create or replace function foo (p_credit_card_number bigint)
  returns void
  language 'plpgsql'
as $$
    v_credit_card  credit_card%type;
begin
    select * 
      into v_credit_card
      from credit_card 
      where credit_card_number = p_credit_card_number 
        for update;
    ...
    
    update credit_card
      set ... 
    where credit_card =  v_credit_card ;
      
end;
$$;

A more common usage though is to create a cursor and update using 'where current of'

create or replace function foo (p_credit_card_number bigint)
  returns void
  language 'plpgsql'
as $$
    c_credit_cards cursor for 
        select * 
          from credit_card 
         where credit_card_number = p_credit_card_number 
           for update;
    v_credit_card  credit_card%type;
begin

    for v_cc in c_credit_cards 
    loop
       ... additional processing ... 
    
       update credit_card
          set ... 
        where current of c_credit_cards;
    end loop; 
   ... 
end;

But note that when the cursor is opened ALL rows in it are locked.

I will accept this, but I'd like to point out that `PERFORM * FROM credit_card WHERE credit_card_number = p_credit_card_number FOR UPDATE` works just as well — puk, Nov 01 '20 at 20:11

How do you use SELECT...FOR UPDATE in a function?

1 Answers1