Postgresql - Combining two queries in a function

Question

I am creating a function where I want to check if a user exists in my users table before inserting a message into my messages table.

SELECT user_id from users WHERE username = 'user1'

If the user exists, the query returns the user_id and my second query uses the user_id to insert a new message into my "messages" table.

user_id would be used in the recipient_id parameter for the query below:

INSERT INTO messages(thread_id, date_posted, subject, body, creator_username, 
    creator_id, is_root, recipient_username, recipient_id, recipient_read)
    VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)

If both queries succeed, the function would return any identifer true/false/null/ or "Some sort of string" so that I can process it in node. And if the first query fails and it can't find a user with the username "user1" it would skip the second query and then return another identifier so I can process the query result. How would I go upon doing this?

Jeremy · Accepted Answer · 2019-11-12 16:49:22Z

1

You can do this in one query, using INSERT... SELECT:

INSERT INTO messages(thread_id, date_posted, subject, body, creator_username, 
    creator_id, is_root, recipient_username, recipient_id, recipient_read)
SELECT $1, $2, $3, $4, $5, $6, $7, $8, user_id, $10
FROM users WHERE username = 'user1'
RETURNING recipient_id;

If the user does not exist, nothing will be inserted or returned. If the user does exist, you'll get the user_id returned.

answered Nov 12, 2019 at 16:49

Jeremy

6,77322 silver badges21 bronze badges

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

1 Comment

WHOATEMYNOODLES Over a year ago

Thanks a bunch! much appreciated

Collectives™ on Stack Overflow

Postgresql - Combining two queries in a function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related