时间戳

首页 > 解决方案 > 我需要捕获所有 product_id 以便我可以在 sql in 子句中使用它

postgresql - 我需要捕获所有 product_id 以便我可以在 sql in 子句中使用它

问题描述

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

多行。

标签： postgresqlplpgsql

解决方案

将条件直接放在EXECUTE. 出于显示目的，用于STRING_AGG显示逗号分隔的 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;

推荐阅读