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.

I am a bit confused. Your two return statements return different numbers of values. — Gordon Linoff
– Gordon Linoff, Commented Feb 8, 2020 at 12:35

Gordon Linoff · Accepted Answer · 2020-02-08 12:40:12Z

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;

answered Feb 8, 2020 at 12:40

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Conditionally perform SQL statements in a raw postgresql function?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related