PostgreSQL function usage

 

Test environment: win2003 + postgresql8.3 (postgresql89.0 is not easy to use)

 

1. Create a database language

1. Open the Start Menu/Program/PostgreSQL 8.3/command prompt

2. Run the "createlang-u Postgres plpgsql S" command"

For example, (E:/program files/PostgreSQL/8.3/bin> createlang-u Postgres plpgsql Postgres)

 

2. Create a database

Create Table co_schedule (n_progid int, dt_starttime timestamp, dt_endtime timestamp );

3. Create a function:
Create Function add_program_time (int4, timestamp, int4, int4, int4) returns bool'
Declare
Prog_id alias for $1;
Duration_min alias for $3;
Period_min alias for $4;
Repeat_times alias for $5;
I int;
Starttime timestamp;
Ins_starttime timestamp;
Ins_endtime timestamp;
Begin
Starttime: = $2;
I: = 0;
While I <repeat_times Loop
Ins_starttime: = starttime;
Ins_endtime: = timestamp_pl_interval (ins_starttime, cast (duration_min | ''mins' as interval ));
Starttime: = timestamp_pl_interval (ins_starttime, cast (period_min | 'mins' as interval ));
Insert into co_schedule values (prog_id, ins_starttime, ins_endtime );
I: = I + 1;
End loop;
If I <repeat_times then
Return false;
Else
Return true;
End if;
End;
'Language 'plpgsql ';

4. Execute Functions
Select add_program_time (1, '2017-10-20 ', '5', '2017010', '5 ');

 

5. view the result after the function is run:
Select * From co_schedule;