Same queries in PostgreSQL stored procedure

Question

So, I'm trying to create a procedure that is going to find a specific row in my table, save the row in a result to be returned, delete the row and afterwards return the result.

The best thing I managed to do was the following:

CREATE OR REPLACE FUNCTION sth(foo integer)
RETURNS TABLE(a integer, b integer, ... other fields) AS $$
DECLARE
    to_delete_id integer;
BEGIN
    SELECT id INTO to_delete_id FROM my_table WHERE sth_id = foo LIMIT 1;
    RETURN QUERY SELECT * FROM my_table WHERE sth_id = foo LIMIT 1;

    DELETE FROM my_table where id = to_delete_id;
END;
$$ LANGUAGE plpgsql;

As you see, I have 2 SELECT operations that pretty much do the same thing (extra overhead). Is there a way to just have the second SELECT and also set the to_delete_id so I can delete the row afterwards?

Richard Huxton · Accepted Answer · 2013-10-04 11:53:25Z

6

You just want a DELETE...RETURNING.

DELETE FROM my_table WHERE sth_id=foo LIMIT 1 RETURNING *

Edit based on ahwnn's comment. Quite right too - teach me to cut + paste the query without reading it properly.

DELETE FROM my_table WHERE id = (SELECT id ... LIMIT 1) RETURNING *

edited Oct 4, 2013 at 11:53

answered Oct 4, 2013 at 11:39

Richard Huxton

23.6k5 gold badges43 silver badges54 bronze badges

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

5 Comments

user330315 Over a year ago

DELETE doesn't support LIMIT

insumity Over a year ago

The second solution seems to pretty much do 2 SELECT's in the sense that you first select the id and then you search to delete the table with this id. I just want to SELECT and delete immediately.

Richard Huxton Over a year ago

But unless I misunderstood your example, you're not trying to delete all the matches, just one (at random)

insumity Over a year ago

Richard ... not really. I forgot to add an 'ORDER BY' I have on my code, so I want to delete only the first element after ordering

insumity Over a year ago

Anyway, accepted your solution as the one most close to what I was looking for.

user330315 · Accepted Answer · 2013-10-04 11:47:09Z

1

Can be done much easier:

CREATE OR REPLACE FUNCTION sth(foo integer)
RETURNS SETOF my_table
AS
$$
BEGIN
    return query
      DELETE FROM my_table p
      where sth_id = foo
      returning *;
END;
$$ 
LANGUAGE plpgsql;

answered Oct 4, 2013 at 11:47

user330315

Comments

Bohemian · Accepted Answer · 2013-10-04 11:36:50Z

0

Select all the columns into variables, return them, then delete using the id:

Declare a variables for each column (named by convention the save as the column but with a leading underscore), then:

SELECT id, col1, col2, ...
INTO _id, _col1, _col22, ...
FROM my_table
WHERE sth_id = foo
LIMIT 1;

RETURN QUERY SELECT _id, _col1, _col22, ...;

DELETE FROM my_table where id = _id;

answered Oct 4, 2013 at 11:36

Bohemian♦

427k103 gold badges604 silver badges750 bronze badges

Collectives™ on Stack Overflow

Same queries in PostgreSQL stored procedure

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related