PostgreSQL combine multiple EXECUTE statements

Question

I have a PREPARE statement which is being called multiple times using EXECUTE. To save database connection cost, we make a big query like:

PREPARE updreturn as update myTable set col1 = 1 where col2= $1 returning col3;
EXECUTE updreturn(1);
EXECUTE updreturn(2);
....
EXECUTE updreturn(10);

and send to the database. However, I get the result for only the last EXECUTE statement.

Is there a way I could store these results in a temporary table and get all the results?

Eggplant · Accepted Answer · 2015-09-09 17:03:02Z

1

You can use a transaction and a temporary table. And execute 3 queries:

Query 1: Start a Transaction (I don't know what you are using to connect to the database).

Query 2:

-- Create a Temporary Table to store the returned values
CREATE TEMPORARY TABLE temp_return (
    col3    text
) ON COMMIT DROP;

-- Prepare the Statement
PREPARE updreturn AS 
    WITH u AS (
        UPDATE myTable SET col1 = 1 WHERE col2= $1 RETURNING col3
    )
    INSERT INTO temp_return (col3) SELECT col3 FROM u;

EXECUTE updreturn(1);
EXECUTE updreturn(2);
.....
EXECUTE updreturn(10);

-- Deallocate the Statement
DEALLOCATE updreturn;

-- Actually return the results
SELECT * FROM temp_return;

Query 3: Commit the Transaction (see note at Query 1)

Without any other details about your complete scenario I can't tell you more, but you should get the idea.

answered Sep 9, 2015 at 17:03

Eggplant

2,0231 gold badge16 silver badges24 bronze badges

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

Comments

Juan Carlos Oropeza · Accepted Answer · 2015-09-09 16:27:10Z

1

I think you need a hack for that.

Create a result table to store your results
Create a trigger before update on myTable
Inside that trigger add INSERT INTO result VALUES(col3)

So every time your myTable row is update also a value will be insert into result

answered Sep 9, 2015 at 16:27

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Collectives™ on Stack Overflow

PostgreSQL combine multiple EXECUTE statements

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related