SQL: Reuse value returned by function?

Question

How do I reuse the value returned by pair called in the function below?
```
CREATE FUNCTION messages_add(bigint, bigint, text) RETURNS void AS $$
    INSERT INTO chats SELECT pair($1, $2), $1, $2
        WHERE NOT EXISTS (SELECT 1 FROM chats WHERE id = pair($1, $2));
    INSERT INTO messages VALUES (pair($1, $2), $1, $3);
$$ LANGUAGE SQL;
```
I know that the SQL query language doesn't support storing simple values in variables as does a typical programming language. So, I looked at WITH Queries (Common Table Expressions), but I'm not sure if I should use WITH, and anyway, I couldn't figure out the correct syntax for what I'm doing.
Here's my SQLFiddle and my original question about storing chats & messages in PostgreSQL. This function inserts-if-not-exists then inserts. I'm not using a transaction because I want to keep things fast, and storing a chat without messages is not so bad but worse the other way around. So, query order matters. If there's a better way to do things, please advise.
I want to reuse the value mainly to speed up the code. But, does the SQL interpreter automatically optimize the function above anyway? Still, I want to write good, DRY code.

score 2 · Accepted Answer · edited Mar 15 '14 at 21:58

2

Since the function body is procedural, use the plpgsql language as opposed to SQL:

CREATE FUNCTION messages_add(bigint, bigint, text) RETURNS void AS $$
BEGIN
    INSERT INTO chats
        SELECT pair($1, $2), $1, $2
        WHERE NOT EXISTS (SELECT 1 FROM chats WHERE id = pair($1, $2));
    INSERT INTO messages VALUES (pair($1, $2), $1, $3);
END
$$ LANGUAGE plpgsql;

Also, if the result to reuse is pair($1,$2) you may store it into a variable:

CREATE FUNCTION messages_add(bigint, bigint, text) RETURNS void AS $$
DECLARE
    pair bigint := pair($1, $2);
BEGIN
    INSERT INTO chats
        SELECT pair, $1, $2
        WHERE NOT EXISTS (SELECT 1 FROM chats WHERE id = pair);
    INSERT INTO messages VALUES (pair, $1, $3);
END
$$ LANGUAGE plpgsql;

edited Mar 15 '14 at 21:58

ma11hew28

121,420
116
450
651

answered Mar 14 '14 at 21:18

Daniel Vérité

58,074
15
129
156

Why does the function body being procedural merit using PL/pgSQL? – ma11hew28 Mar 15 '14 at 19:04
@MattDiPasquale: PL stands for procedural language, it provides what SQL lacks in terms of variables and execution control. The advantage of SQL as a func language is the potential inlining of single SELECTs, but it doesn't apply in this case of two INSERTs. – Daniel Vérité Mar 17 '14 at 12:43

Clodoaldo Neto · Answer 2 · 2014-03-14T21:33:48.947

1

create function messages_add(bigint, bigint, text) returns void as $$
    with p as (
        select pair($1, $2) as p
    ), i as (
        insert into chats
        select (select p from p), $1, $2
        where not exists (
            select 1
            from chats
            where id = (select p from p)
        )
    )
    insert into messages
    select (select p from p), $1, $3
    where exists (
        select 1
        from chats
        where id = (select p from p)
    )
    ;
$$ language sql;

It will only insert into messages if it exists in chats.

edited Mar 14 '14 at 21:33

answered Mar 14 '14 at 18:22

Clodoaldo Neto

118,695
26
233
260

Thanks, Clodoaldo! But, I want it to insert into `messages` even if the row already exists in `chats` so that you can add multiple messages to one chat. By chat, I mean conversation, or thread. – ma11hew28 Mar 14 '14 at 20:24

score 0 · Answer 3 · edited May 23 '17 at 11:46

I actually don't have an answer for this part of your question, but I'll address it anyway because Markdown SUCKS and Stack Overflow doesn't support the start attribute on the ol element.
OK. Now, that we're at 2, :-) PostgreSQL functions are transactional. So, order actually doesn't matter since both inserts will be committed together in one transaction.
No, the optimizer only pre-evaluates immutable functions when they're called with constant (not variable) arguments, e.g., pair(4, 5). See Function Volatility Categories.

SQL: Reuse value returned by function?

3 Answers3