CREATE OR REPLACE FUNCTION getXXXList()
RETURNS SETOF record AS
$BODY$
DECLARE
result RECORD;
BEGIN
for result in
select field1,...
from table1...
LOOP
RETURN NEXT result;
END LOOP;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION getXXXList() OWNER TO postgres;参考了网上的例子,编译通过了。
但是测试的时候
select getXXXXXList()提示
RETURN NEXT result;
返回类型不正确
你声明的时候用了虚类型(RECORD)
所以必须在调用的时候声明类型
select * from getXXXXXList() as (字段名称1 类型,字段名称2 类型,....)
select * from getXXXXXList() as (字段名称1 类型,字段名称2 类型,....)for i=1 ...result.size....
next i
TYPE typeXXX IS RECORD(
field1 NUMBER(10),
field2 VARCHAR2(6)); TYPE tableXXX IS TABLE OF typeXXX INDEX BY BINARY_INTEGER;
这样的写法吗?
CREATE FUNCTION dt1() RETURNS SETOF record AS $$
declare r record;
ff varchar:='SELECT "A",';
begin
for r in select distinct "B" from tth1 loop
ff:=ff || 'max(case when "B"='''||r."B" || '''' || ' then "C" ' || ' end ),';
RAISE NOTICE '123(%)', ff;
end loop;
ff:=substr(ff,1,length(ff)-1) || ' from tth1 group by "A"';
FOR r IN execute ff Loop
RETURN NEXT r;
END LOOP;
end;
$$ LANGUAGE plpgsql;select * from dt1() a(q text,d float,e float,f float);ORCREATE FUNCTION dt2(refcursor) RETURNS refcursor AS $$
declare r record;
ref refcursor;
ff varchar:='SELECT "A",';
begin
for r in select distinct "B" from tth1 loop
ff:=ff || 'max(case when "B"='''||r."B" || '''' || ' then "C" ' || ' end ) as ' ||r."B" || ' ,';
RAISE NOTICE '123(%)', ff;
end loop;
ff:=substr(ff,1,length(ff)-1) || ' from tth1 group by "A"';
open $1 for execute ff;
return $1;
end;
$$ LANGUAGE plpgsql;
BEGIN;
SELECT dt2('a');
FETCH ALL IN a;
commit;
create type xxx as (
field1 NUMBER(10),
field2 VARCHAR2(6))CREATE FUNCTION dt1() RETURNS SETOF xxx
declare
myrec record;
...
begin
....
for myrec in select * from getXXXXXList() as (字段名称1 类型,字段名称2 类型,....)
loop
....
end loop;
....
for r in select distinct "B" from tth1 loop
RETURNS void AS
$BODY$
DECLARE
text_output varchar;
result RECORD;BEGINfor result in select * from geXXXXList() as (field1 numeric,field2 numeric,...);
LOOP
text_output := text_output || row_data.title || ''\n'';
END LOOP;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION setXXXInfo() OWNER TO postgres;如上代码,希望在setXXXInfo函数中调用getXXXXList函数,在循环getXXXXList函数的时候输出某个字段的值(这部分会扩展为调用其它函数)
CREATE OR REPLACE FUNCTION setXXXInfo()
RETURNS void AS
$BODY$
DECLARE
text_output varchar;
result RECORD;
BEGINfor result in select * from getXXXXList() as (field1 numeric,field2 numeric,...);
LOOP
text_output := text_output || result .title || ''\n'';
END LOOP;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ALTER FUNCTION setXXXInfo() OWNER TO postgres;
for result in select * from getXXXXList() as (field1 numeric,field2 numeric,...);
LOOP
text_output := text_output || result .title || ''\n'';
END LOOP;测试了一下可以这样写,多谢大家的回答。