CODEWITHSUNDEEP
postgresqlplpgsql
Satyajeet
Satyajeet

Reputation: 15

i need to capture all product_id so i can use it in sql in clause

CREATE OR replace FUNCTION testfunction(timestamp) returns void 
AS 
  $body$ 
  DECLARE 
    product_ids text; 
  BEGIN 
    -- this is returning multiple rows, but it assigning only one to  prodcut_ids variable. 
    SELECT DISTINCT product_id AS product_id 
    INTO            product_ids 
    FROM            test_product 
    WHERE           created_on > $1::timestamp 
    ORDER BY        product_id ; 

    RAISE notice 'product IDs : %', product_ids; 
    EXECUTE 'copy (SELECT * FROM test_product WHERE product_id in (' 
    || product_ids 
    || ' ) ) TO ''C:\projects\test_product.csv'' CSV HEADER'; 
  END $body$ LANGUAGE plpgsql volatile;

-- it is only exporting one record even the above select returning

multiple rows.

Upvotes: 0

Views: 49

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

Put the condition directly within the EXECUTE. For display purpose, use STRING_AGG to show comma separated product_ids.

CREATE OR replace FUNCTION testfunction ( timestamp ) returns void 
AS 
  $body$ 
  DECLARE 
    v_created_on timestamp := $1;
  BEGIN 
    SELECT STRING_AGG( product_id,',' ORDER BY product_id) 
    INTO            product_ids 
      FROM            test_product 
       WHERE          created_on > v_created_on; 

    RAISE notice 'product IDs : %', product_ids; 
    EXECUTE 'copy ( SELECT * FROM test_product WHERE created_on >  $1 ) 
    TO ''C:\projects\test_product.csv'' CSV HEADER' USING v_created_on; 
  END; 
 $body$ LANGUAGE plpgsql volatile;

Upvotes: 1

Related Questions