CODEWITHSUNDEEP
sqlpostgresqlplpgsql
WHOATEMYNOODLES
WHOATEMYNOODLES

Reputation: 897

Combining these queries into a function that returns

I have a couple of queries that I'm trying to combine together into a function that returns some values back. Except I can't figure out how to do it correctly.

This is the logic im trying to output:

if((SELECT parent_id FROM event_comments WHERE comment_id = $1) == null){

    WITH cte AS (DELETE FROM event_comments WHERE thread_id = '1BZbAR'
    RETURNING parent_id, comment_id, thread_id)
    SELECT parent_id, comment_id, thread_id
    FROM cte
    WHERE parent_id IS NULL;

}else{
    DELETE FROM event_comments WHERE comment_id = $1 AND created_by = $3
    returning parent_id, comment_id, thread_id
}

Getting the syntax error type "cte" does not exist? 

CREATE FUNCTION deleteComment(comment_id integer, created_by UUID, thread_id integer) 
RETURNS cte AS $$

BEGIN
CASE WHEN (SELECT parent_id FROM event_comments WHERE comment_id = $1) is NULL
THEN 
    WITH cte AS (
        DELETE FROM event_comments WHERE thread_id = $3
        RETURNING parent_id, comment_id, thread_id
    )
    SELECT parent_id, comment_id, thread_id
    FROM cte
    WHERE parent_id IS NULL;
ELSE
    WITH cte AS (
        DELETE FROM event_comments WHERE comment_id = $1 AND created_by = $2
        returning parent_id, comment_id, thread_id
    )

END; $$
LANGUAGE plpgsql;

Upvotes: 1

Views: 48

Answers (1)

Jim Jones
Jim Jones

Reputation: 19643

In a plpgsql function you have to specify exactly what you're returning, and cte ins't a data type but a variable you used inside your function. That being said, you should first declare the columns you're returning and their data types. You can create a data type on your own if you wish, but the easiest way is to return a table:

CREATE OR REPLACE FUNCTION 
  deleteComment(comment_id_in INT, created_by_in UUID, thread_id_in INT) 
RETURNS TABLE (parent_id_out INT, comment_id_out INT, thread_id_out INT) AS $$
BEGIN
  IF (SELECT parent_id FROM event_comments WHERE comment_id = $1) IS NULL THEN
    RETURN QUERY DELETE FROM event_comments 
      WHERE thread_id = $3
      RETURNING parent_id, comment_id, thread_id;
  ELSE
    RETURN QUERY DELETE FROM event_comments 
      WHERE comment_id = $1 AND created_by = $2
      RETURNING parent_id, comment_id, thread_id;
  END IF;
END;
$$ LANGUAGE plpgsql;

Sample data:

CREATE TEMPORARY TABLE event_comments 
(comment_id INT, parent_id INT, thread_id INT, created_by UUID);
INSERT INTO event_comments VALUES (1,1,1,'123e4567-e89b-12d3-a456-426655440000');

Testing:

SELECT * FROM deleteComment(1,'123e4567-e89b-12d3-a456-426655440000',4);

 parent_id_out | comment_id_out | thread_id_out 
---------------+----------------+---------------
             1 |              1 |             1
(1 Zeile)

Check this answer for a more compact example.

Upvotes: 2

Related Questions