Create a temp table, loop, add data, select from it? "Query has no destination for result data"

Question

I am creating a temp table, and am fetching data from multiple tables and inserting data into it. When I try to get the data back from the table I get an error:

[42601] ERROR: query has no destination for result data
Hint: If you want to discard the results of a SELECT, use PERFORM instead.

do
$$
    DECLARE
        sampleproductId   varchar;
        productIds        text[] := array [
            'abc1',
            'abc2'
            ];
        tId              varchar;
        DECLARE result  jsonb;
        DECLARE resultS jsonb[];
    begin
       CREATE TEMP TABLE IF NOT EXISTS product_temp_mapping
        (
            accountid      int,
            productUID      varchar,
            sampleproductId text
        );
        FOREACH sampleproductId IN ARRAY productIds
            LOOP
                tId := (select id
                        from product.product
                        where uid = sampleproductId);
                INSERT into product_temp_mapping (accountid, productUID, sampleproductId)
                select accountid, tId, sampleproductId
                from product.accountproductmap
                where productId = cast(tId as int);
            END LOOP;
        select * from product_temp_mapping;
    end ;
$$;

Is this the right way to do it? This is the first time I am doing something with a temp table.

Why do you need a loop and a temp table in the first place? From your example it looks as if a declarative approach would be sufficient. — Lennart - Slava Ukraini, Mar 30 '21 at 01:51
@Lennart I need to loop on list and get the data for all the items. What do you mean by declarative approach? — Aijaz, Mar 30 '21 at 01:56
You can also use a derived table or a IN predicate instead of the array — Lennart - Slava Ukraini, Mar 30 '21 at 02:03
I can use a join but not in a loop right? As in the first place I need to fetch tId from one table, and then use that to get data from another. So basicallyits three layers. Looping from the declared array, getting data from one table, and then using tId to get data from another table. — Aijaz, Mar 30 '21 at 02:05
I suggest you create a db<>fiddle with some sample data. You dont need the loop nor the array — Lennart - Slava Ukraini, Mar 30 '21 at 02:09
In addition, does the question concern both mysql and postgresql? A procedural approach will be a lot trickier if you need to support several DBMS — Lennart - Slava Ukraini, Mar 30 '21 at 02:12

Erwin Brandstetter · Accepted Answer · 2021-03-30T03:49:08.320

When I try to get the data back from the table I get an error.

That's because you cannot return anything from a DO statement. You would need a function where you define the return type in the RETURNS clause. (But you still wouldn't need a temporary table.)
Plus, PL/pgSQL does not allow SELECT without a target.

But you don't need any of this for the example. A plain query does it:

SELECT a.accountid::int, p.id::varchar AS product_uid, a.sampleproductId::text
FROM   product.product p
JOIN   product.accountproductmap a ON a.productId = p.id::int
WHERE  p.uid = ANY ('{abc1, abc2}'::text[]);

Some of the casts are probably unnecessary.
You might wrap it into an SQL function (PL/pgSQL not needed for this). See:

Aside: use legal, lower-case identifiers! See:

Are PostgreSQL column names case-sensitive?

Create a temp table, loop, add data, select from it? "Query has no destination for result data"

1 Answers1