2

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.

Improve this question
7
  • 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. Commented Mar 30, 2021 at 1:51
  • @Lennart I need to loop on list and get the data for all the items. What do you mean by declarative approach? Commented Mar 30, 2021 at 1:56
  • Use a join instead of a loop Commented Mar 30, 2021 at 1:59
  • You can also use a derived table or a IN predicate instead of the array Commented Mar 30, 2021 at 2: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. Commented Mar 30, 2021 at 2:05

1 Answer 1

Reset to default
5

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:

Improve this answer
1
  • That's brilliant! Thanks. Commented Mar 30, 2021 at 4:47

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.