求的是用户函数,就是将检索得出来的值RETURN回来。我现在只知道返回一条记录的方法如下 CREATE OR REPLACE FUNCTION getcode(character varying, character varying) RETURNS character AS DECLARE t2_row account_manage%ROWTYPE; N ALIAS FOR $1; P ALIAS FOR $2; BEGIN SELECT * into t2_row FROM account_manage; RETURN t2_row.firm_id ||',' ||t2_row.org_id||','|| t2_row.account; END; 问题是,这种方法取复数行结果我不知道怎么做,求解。 大侠帮忙!
可以加limit 1 只取一条,或者order后取一条
可以返回字符串数组CREATE OR REPLACE FUNCTION getAllFoo() RETURNS SETOF foo AS $BODY$ DECLARE r foo%rowtype; BEGIN FOR r IN SELECT * FROM foo WHERE fooid > 0 LOOP -- can do some processing here RETURN NEXT r; -- return current row of SELECT END LOOP; RETURN; END $BODY$ LANGUAGE 'plpgsql' ;用setof,这是help上的一个样例 你可以用setof charactar
CREATE OR REPLACE FUNCTION getcode(character varying, character varying)
RETURNS character AS
DECLARE
t2_row account_manage%ROWTYPE;
N ALIAS FOR $1;
P ALIAS FOR $2;
BEGIN
SELECT * into t2_row FROM account_manage;
RETURN t2_row.firm_id ||',' ||t2_row.org_id||','|| t2_row.account;
END;
问题是,这种方法取复数行结果我不知道怎么做,求解。
大侠帮忙!
只取一条,或者order后取一条
$BODY$
DECLARE
r foo%rowtype;
BEGIN
FOR r IN SELECT * FROM foo
WHERE fooid > 0
LOOP
-- can do some processing here
RETURN NEXT r; -- return current row of SELECT
END LOOP;
RETURN;
END
$BODY$
LANGUAGE 'plpgsql' ;用setof,这是help上的一个样例
你可以用setof charactar