2

In a plpgsql procedure I am looking how to reference and use a result set that I get from the first query. Following code tries to demonstrate what I want to achieve:

do
$body$
DECLARE
  ref_result_set ???;
BEGIN

  ref_result_set :=  select 'asdf';

  perform xxx from ref_result_set;
  perform yyy from ref_result_set;

END;


$body$
language plpgsql;

I was looking at cursors but there is just an option to fetch row by row and not an entire set. Is there any option how to achieve this without first writing to a table?

martez
  • 107
  • 1
  • 11
  • 1
    You cannot *store* a whole result-set in a variable. You could convert it to an array, but usually better just iterate it row by row. There is also the temporary table concept (which are exist only in that session). Why do you want to do that? `perform xxx from ref_result_set` won't do anything, `do` statements cannot return values. – pozs Jul 09 '14 at 10:15
  • PostgreSQL doesn't support that, unfortunately. You have to use refcursors instead. See the manual - plpgsql, refcursors. – Craig Ringer Jul 09 '14 at 10:46
  • Post the real problem you are trying to solve and you will get suggestions about more sane approaches. – Clodoaldo Neto Jul 09 '14 at 11:46
  • Instead of the perform command we will use insert: `insert into test ( select xxx from ref_result_set);` `insert into test2 ( select xxx from ref_result_set);`. We will probably use temporary tables like mentioned in the first comment – martez Jul 09 '14 at 14:41

1 Answers1

3

Question asked

There are no "table variables" in plpgsql (or SQL). You can use:

Related questions:
Select from a table variable
Function to return a table of all children of a node

Actual problem

For your actual problem I suggest data-modifying CTEs:

WITH sel AS (
   SELECT col1, col2, ..
   FROM   tbl1
   WHERE  <expensive condition>
   )
, ins1 AS (
   INSERT INTO test1 (col1, col2, ..)
   SELECT col1, col2, ..
   FROM   sel
   WHERE  <some condition>
   )
INSERT INTO test2 (col1, col2, ..)
SELECT col1, col2, ..
FROM   sel
WHERE  <some condition>;

You can use that inside plpgsql code or as standalone SQL command.
Inside plpgsql code you can reference variables in the query ...

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    What about converting the result set to an array and storing that in a variable? I'm sure that would only be a good idea if you are confident the result set is small, but would it work? – jpmc26 Oct 04 '14 at 19:50
  • @jpmc26: Possible in side a plpgsql function, yes. But the presented data-modifying CTE is faster and more elegant. Also reduces possibles issues with concurrency. – Erwin Brandstetter Oct 15 '14 at 22:38