最近,在一个Insert SQL时报字段长度不够,但又没指出哪个字段,表的字段数很多,看了好久都没找出是哪个字段,心想有什么办法能按指定条件过滤当前记录的所有字段不就能找出我想要的字段吗?昨天终于用hstore写出了一个满足需求的函数,特记录如下,备查。
--usage:
-- select * from findfield('select * from sales_orders where id=1000','length(value)>=50 and value ilike ''%car%''');
-- Function: public.findfield(text, text);
-- DROP FUNCTION public.findfield(text, text);
CREATE OR REPLACE FUNCTION public.findfield(
IN sql text,
IN wh text,
OUT fldname text,
OUT fldvalue text
)
RETURNS SETOF RECORD AS
$BODY$
DECLARE
sql0 text;
rec record;
BEGIN
if sql='' then
sql := 'select 1 as v';
end if;
if wh='' then
wh := 'false';
end if;
sql0 := 'with h as (' || sql ||' ) ';
sql0 := sql0 || ',h1 as (select hstore(h) v from h)';
sql0 := sql0 || ',h2 as (select a.key as name,a.value from h1,each(h1.v) a)';
sql0 := sql0 || 'select * from h2 where ' || wh || ';';
for rec in EXECUTE sql0 loop
fldname := rec.name;
fldvalue := rec.value;
return next;
end loop;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.findfield(text, text)
OWNER TO root;