postgres 不支持存储过程,只能用function来代替
1.查询出来的数据插入表中
CREATE OR REPLACE FUNCTION addcol(
schemaname varchar,tablename varchar,
colname varchar)
RETURNS varchar
language 'plpgsql'
as
$$
declare
col_name varchar ;
BEGIN
EXECUTE 'select column_name from information_schema.columns where table_schema = ' ||
quote_literal(schemaname)||' and table_name='|| quote_literal(tablename) || ' and column_name= '|| quote_literal(colname)
into col_name ;
raise info ' the val : % ', col_name;
IF(col_name is null )
THEN
col_name := colname;
ALTER TABLE meet ADD COLUMN member character varying(128);
ELSE
col_name := colname ||' Already exist';
END IF;
return col_name;
END;
$$;
SELECT addcol('public', 'meet', 'member');
DROP FUNCTION addcol(schemaname varchar, tablename varchar, colname varchar);
2.游标循环记录
CREATE OR REPLACE FUNCTION add()
RETURNS varchar
language 'plpgsql'
as
$$
begin
declare userInfo RECORD ;
declare seq_id bigint;
declare users refcursor;
begin
OPEN users for EXECUTE 'select id, name, email from user';
fetch users into userInfo;
while userInfo.id > 0 loop
seq_id = nextval('hibernate_sequence');
INSERT INTO ten(id,name,email,type) values( seq_id,userInfo.name,userInfo.email,0);
update rb_user set id = seq_id where id = userInfo.id;
fetch users into userInfo;
end loop;
close users;
return '';
end;
end
$$;
--run
SELECT add();
DROP FUNCTION add();