How to use stored procedure returning boolean in IF-THEN-ENDIF condition?

Question

In a PostgreSQL table for a word game I keep track of paying players either by the column vip_until or by the column grand_until having a valid date in future:

create table users (
        uid serial primary key,
        vip_until timestamp null,  -- date in future indicates paying customer
        grand_until timestamp null -- date in future indicates paying customer
);

I have written a short stored procedure to check that:

create or replace function is_vip(
    IN in_uid integer,
    OUT out_vip boolean
) as $BODY$
        BEGIN
                out_vip := exists(select 1 from users 
                           where uid = in_uid and 
                           greatest(vip_until, grand_until) > current_timestamp);
        END;
$BODY$ language plpgsql;

Then I am trying to use the above function in another stored procedure:

create or replace function join_new_game(
    IN in_uid integer,
    IN in_letters varchar(130),
    IN in_style integer,
    OUT out_gid integer
) as $BODY$
        BEGIN
        /* maybe there is a new game already, just waiting for the player's 1st move*/
        select gid into out_gid from games 
        where (player1 = in_uid and stamp1 is null) 
        or (player2 = in_uid and stamp2 is null) limit 1;

        IF not found THEN
                /* try to find games having just 1 player (with different uid) */
                select gid into out_gid from games 
                where (player1 != in_uid and stamp1 is not null
                and player2 is null) limit 1;

                IF not found THEN
                        /* only allow board style 1 for non-paying customers */
                        IF not select is_vip(in_uid) THEN
                                in_style := 1;  -- the above line fails
                        END IF;

                        /* create new game with player1 = uid and stamp1 = null */
                        insert into games (
                                created, 
                                player1, 
                                stamp1, 
                                stamp2, 
                                letters1, 
                                letters2, 
                                letters, 
                                board, 
                                style 
                        ) values (
                                current_timestamp, 
                                in_uid, 
                                null, 
                                null, 
                                substring(in_letters, 1, 7), 
                                substring(in_letters, 8, 7), 
                                substring(in_letters, 15), 
                                rpad('', 225), -- fill 15x15 board
                                in_style
                        ) returning gid into out_gid;
                ELSE
                        update games set player2 = in_uid where gid = out_gid;
                END IF;
        END IF;
        END;
$BODY$ language plpgsql;

But I get this syntax error:

ERROR:  syntax error at or near "select"
LINE 21:                         IF not select is_vip(in_uid) TH...
                                        ^

How to use the is_vip() function properly?

Strictly speaking, [Postgres does not have "stored procedures" at all](http://stackoverflow.com/questions/28250376/do-stored-procedures-run-in-database-transaction-in-postgres/28251922#28251922). Just functions doing almost, but not quite, the same. — Erwin Brandstetter, Feb 04 '16 at 04:51

klin · Answer 1 · 2016-02-04T09:41:15.347

3

is_vip(in_uid) is a function that returns Boolean. You can call it directly:

IF not is_vip(in_uid) THEN

or, if you want to use select:

IF not (select is_vip(in_uid)) THEN

If you want to use a scalar query as a value expression the query must be parenthesized.

edited Feb 04 '16 at 09:41

answered Feb 03 '16 at 20:04

klin

112,967
15
204
232

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

How to write the `is_vip()` function properly?

Your function can be more efficient. Make it a STABLE SQL function, so it can be inlined.

CREATE OR REPLACE FUNCTION is_vip(in_uid integer)
  RETURNS boolean AS
$func$
SELECT EXISTS (
   SELECT 1 FROM users
   WHERE  uid = in_uid
   AND   (vip_until   > current_timestamp OR
          grand_until > current_timestamp)
   )
$func$ LANGUAGE sql STABLE;

Ideally, you have a multicolumn index to allow index-only scans on:

(uid, vip_until, grand_until)

uid must be the first column.

`How to use the is_vip function properly?`

@kin already provided a fix for your basic syntax error: Use your own functions just like any other Postgres function. But there is a lot more here:

I formatted the most important pieces bold:

CREATE OR REPLACE FUNCTION join_new_game(
    IN in_uid integer,
    IN in_letters varchar(130),
    IN in_style integer,
    OUT out_gid integer) AS
$func$
BEGIN
   /* maybe there is a new game already, just waiting for the player's 1st move*/
   SELECT gid INTO out_gid
   FROM   games 
   WHERE (player1 = in_uid AND stamp1 IS NULL) 
   OR    (player2 = in_uid AND stamp2 IS NULL)
   LIMIT 1;

   IF NOT FOUND THEN
      /* try to find games having just 1 player (with different uid) */
      /* and UPDATE immediately using a smart locking strategy */
      UPDATE games g
      SET    player2 = in_uid
      FROM  (
         SELECT gid
         FROM   games 
         WHERE  player1 <> in_uid
         AND    stamp1 IS NOT NULL
         AND    player2 IS NULL
         LIMIT  1
         FOR    UPDATE SKIP LOCKED  -- see link below !!
         ) g1
      WHERE   g.gid = g1.gid
      RETURNING g.gid
      INTO    out_gid;

      IF NOT FOUND THEN
         /* create new game with player1 = uid and stamp1 = null */
         INSERT INTO games (created, player1, stamp1, stamp2, letters1, letters2, letters, board, style)
         VALUES (current_timestamp, in_uid, null, null
               , left(in_letters, 7)
               , substring(in_letters, 8, 7)
               , right(in_letters, -15)  -- guessing you want to start at pos 16!
               , rpad('', 225)  -- fill 15x15 board
               /* only allow board style 1 for non-paying customers */
               , CASE WHEN NOT is_vip(in_uid) THEN 1 END  -- defaults to NULL
               )
         RETURNING gid
         INTO  out_gid;
      END IF; 
   END IF;
END
$func$  LANGUAGE plpgsql;

Fortunately, you are using the latest version of Postgres (9.5), which introduced the new smart locking strategy for queuing with FOR UPDATE SKIP LOCKED. Detailed explanation:

Postgres UPDATE … LIMIT 1

I also inlined the is_vip() call into the INSERT query with a CASE expression. That's more efficient.

Several other minor optimizations.

Erwin, have you missed brackets around OR-condition in `is_vip` function? And would using `greatest(vip_until,grand_until) > current_timestamp` in that spot be a bad idea because the index on `(uid, vip_until, grand_until)` wouldn't be used? — Alexander Farber, Feb 04 '16 at 09:38
@AlexanderFarber: Brackets, yes, thanks, fixed. And yes, the idea was to make the expression sargable. (Then I realized that `uid` is the primary key anyway, so only little to gain here.) The alternative form should be a tiny bit faster even without index. — Erwin Brandstetter, Feb 04 '16 at 13:51

score 1 · Answer 3 · answered Feb 03 '16 at 20:02

1

Try changing using schema.function_name like this:

IF (<schema>.is_vip(in_uid) = 'false')
THEN
   ...
END IF;

answered Feb 03 '16 at 20:02

aelkz

1,786
1
24
26

How to use stored procedure returning boolean in IF-THEN-ENDIF condition?

3 Answers3

How to write the is_vip() function properly?

How to use the is_vip function properly?

How to write the `is_vip()` function properly?

`How to use the is_vip function properly?`