数组赋值，存储过程和方法的使用

构造数据：

drop table if exists people;

CREATE TABLE people (
	idcard int8 NOT NULL,
	username varchar(200) NOT NULL,
	address varchar(200) NOT NULL
);

INSERT INTO health_manager.people
(idcard, username, address)
VALUES(19790429	,'ztt',	'北京市海淀区上庄镇'),
(19690421	,'ztt1'	,'北京市海淀区西北旺镇'),
(19890429	,'ztt2'	,'北京市海淀区永丰'),
(19790425	,'ztt3'	,'北京市海淀区永丰1'),
(19690426	,'ztt4'	,'北京市海淀区永丰2'),
(19890414	,'ztt5'	,'北京市海淀区永丰3'),
(19890415	,'ztt6'	,'北京市海淀区永丰40');

法一：存储过程

CREATE OR REPLACE PROCEDURE allnames()  AS 
$$
declare 
names text[];
v_sql text;
tmp_table_name text;
begin
	names := (select array_agg(distinct username) names from people);
	tmp_table_name:='tmp_name';
	v_sql = format('drop table if exists %s',tmp_table_name);
    execute v_sql;
    v_sql = format('create table %s (username text not null)',tmp_table_name);
    execute v_sql;
	for i in 1..array_length(names,1)
		loop
		v_sql = format('insert into %s(username)  select cast(''%s'' as text)',tmp_table_name,names[i]);	
		execute v_sql;
		end loop;
end;
$$ language plpgsql;

调用：

call allnames()

查询：

select * from tmp_name

法二：function

drop function if exists put_names cascade;
create or replace function put_names(names text[],tmp_name text) returns void as
$$
declare
	v_sql text;
begin
	v_sql = format('drop table if exists %s',tmp_name);
    execute v_sql;
    v_sql = format('create table %s(username text not null)',tmp_name);
    execute v_sql;
	for i in 1..array_length(names,1)
		loop
		v_sql = format('insert into %s(username) select cast(''%s'' as text)',tmp_name,names[i]);	
		execute v_sql;
		end loop;

end;
$$ language plpgsql;

调用：

with nm as(select array_agg(distinct username) names from people)

select put_names(nm.names,'tmp_name') from nm

查询：

select * from tmp_name