DO            --关键字

$serch$       --$中间随便写， 不能用特殊符号和数字好像$

 DECLARE times integer; --定义变量

 rec history_depart%ROWTYPE; -- 定义行变量

 BEGIN

    times := 0;

    FOR rec in SELECT * FROM history_depart WHERE person_id = 5655 LOOP --几种循环的一种 循环查到的结果 另几种查看 https://www.cnblogs.com/stephen-liu74/archive/2012/06/06/2312759.html

    times:=times+1;

    RAISE NOTICE 'LOOP % %',times,rec.history_depart_id;

  END LOOP;

END

$serch$ LANGUAGE PLPGSQL

DO

$serch$

DECLARE rec history_depart%ROWTYPE;

strSQL varchar(1000);

BEGIN

  strSQL := '';

  FOR rec in SELECT * FROM history_depart WHERE person_id = 5655 LOOP

    strSQL :=strSQL || ' union ';

    RAISE NOTICE 'LOOP % %',strSQL,rec.history_depart_id;

    strSQL := strSQL || 'SELECT * FROM attend_record_all AS a LEFT JOIN leave_type AS b ON a.leave_type_id = b.leave_type_id WHERE attend_day >= ''' || to_char(rec.transfer_in,'YYYY-MM-DD HH24:MI:SS') || ''' AND attend_day < ''' || to_char(COALESCE(rec.transfer_out,'9999/12/31 23:59:59'),'YYYY-MM-DD HH24:MI:SS') || ''' AND person_id = 5655 AND a.leave_type_id < 50';

    RAISE NOTICE 'LOOP % %',strSQL,rec.history_depart_id;

  END LOOP;

  strSQL := substring(strSQL from 7); 

  RAISE NOTICE '%',strSQL;

  EXECUTE strSQL;

END

$serch$