postgres 預存程序 遊標 函數

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();