PG outputdata函数

根据表名，打印出该表的insert语句。

函数跟ms sql2005 里的存储过程用法类似。

直接在需要的库里执行该函数。

CREATE OR REPLACE FUNCTION outputdata(

    tablename text,

    sqlstr text)

  RETURNS SETOF record AS

$BODY$

declare insertstr text;

declare v_rec record;

declare v_vas record;

declare v_val text;

begin

insertstr :='''insert into ' || tablename || '(';

v_val:='';

for v_rec in execute 'select attname from pg_class c,pg_attribute attr where relname = ''' || tablename || '''and c.oid = attr.attrelid and atttypid > 0 and attnum>0' loop

insertstr:= insertstr || v_rec.attname || ',' ;

v_val:= v_val || 'case when l_isnull_str('||v_rec.attname||'::text) = ''f'' then ''''''''||' || v_rec.attname || '||'''''''' else ''null'' end || '','' ||';

end loop;

insertstr := substring(insertstr,0,length(insertstr))||')''';

v_val := ''' values(''||' || substring(v_val,1,length(v_val)-14) || ' end ||'');''';

for v_vas in execute 'select '||insertstr || ' || ' || v_val || ' as sql from ' || tablename || ' ' || sqlstr  loop

raise info '%', v_vas.sql;

end loop;

end;

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100

  ROWS 1000;

ALTER FUNCTION outputdata(text, text)

  OWNER TO postgres;

使用方法:select outputdata(‘表名’,’查询条件’)，查询条件为空时，引号内容为空。

执行后会在控制台消息里打印出来。

例：select outputdata('leaptable','');

Select outputdata('leaptable','where id=''aaa''');