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.

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. 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?
    – Aijaz
    Mar 30, 2021 at 1:56
  • Use a join instead of a loop Mar 30, 2021 at 1:59
  • You can also use a derived table or a IN predicate instead of the array 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.
    – Aijaz
    Mar 30, 2021 at 2:05

1 Answer 1

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:

1
  • That's brilliant! Thanks.
    – Aijaz
    Mar 30, 2021 at 4:47

Not the answer you're looking for? Browse other questions tagged or ask your own question.