sql - Conditionally perform SQL statements in a raw postgresql function?

Question

I'm not exactly clear on whether or not it's possible to conditionally perform SQL statements using raw SQL functions in Postgres, or if you need to use plpgsql with IF statements for that.

Basically, what I'm trying to do is this:

CREATE OR REPLACE FUNCTION public.usp_reply_to_convo(convo_id BIGINT, person_id INTEGER, body TEXT)
  RETURNS TABLE(convo_reply_id BIGINT, person_id integer)
  LANGUAGE sql
AS $function$
-- SELECT timeout_until FROM person WHERE person_id = $1;
-- IF timeout_until > now() RETURN timeout_until
-- ELSE do the below
INSERT INTO convo_reply (convo_id, person_id, body) VALUES ($1, $2, $3);
RETURNING convo_reply_id, person_id;
$function$;

Where the user can reply to a conversation if their timeout_until value is less than now(). And if it isn't, then the query isn't executed, and instead their timeout value is returned.

Is this sort of thing possible with raw SQL, or is plpgsql necessary?

Also, is there a way to handle the fact that this function can return two things? Either it returns the timeout timestamp, or the conversation reply id. Not sure how to handle these competing concerns.

标签： sqldatabasepostgresqlplpgsql

Answer 1

I think you can do what you want with CTEs. You can phrase this something like this:

with p as (
      select timeout_until 
      from person 
      where person_id = $1
     ),
     i as (
      insert into convo_reply (convo_id, person_id, body) 
          select $1, $2, $3
          where (select timeout_until from p) <= now()
          returning convo_reply_id, person_id
    )
select ?
from p left join
     i
     on 1=1;