- 论坛徽章:
- 3
|
回复 1# oracle_1010
在PostgreSQL可以很简单的做到,具体见下面:
create table ADInfo(Adid INT,v_Ver int, Url text, LinkUrl text);
insert into adinfo values(1,1,'www.baidu.com','http://www.baidu.com');
insert into adinfo values(2,2,'www.google.com','http://www.google.com');
你可以创建一个返回类型:
CREATE TYPE ret_my_type AS
(
Ver INT,
Url VARCHAR,
LinkUrl VARCHAR
);
CREATE OR REPLACE FUNCTION my_function(v_adid int) RETURNS SETOF ret_my_type AS
$$
DECLARE
v_rec ret_my_type;
BEGIN
IF EXISTS(select * from ADInfo where ADID=v_ADID) then
FOR v_rec IN select v_Ver as Ver ,Url, LinkUrl from ADInfo where ADID=V_ADID LOOP
RETURN NEXT v_rec;
END LOOP;
ELSE
FOR v_rec IN SELECT v_Ver as Ver ,'' as Url ,'' as LinkUrl LOOP
RETURN NEXT v_rec;
END LOOP;
END IF;
END;
$$
LANGUAGE PLPGSQL;
postgres=# select * from my_function(2);
ver | url | linkurl
-----+----------------+-----------------------
2 | www.google.com | http://www.google.com
(1 row)
如果你不创建类型ret_my_type,可以使用RECORD这种通用类型返回数据,但调用的时候就得指定返回的数据类型:
CREATE OR REPLACE FUNCTION my_function2(v_adid int) RETURNS SETOF RECORD AS
$$
DECLARE
v_rec RECORD;
BEGIN
IF EXISTS(select * from ADInfo where ADID=v_ADID) then
FOR v_rec IN select v_Ver as Ver ,Url, LinkUrl from ADInfo where ADID=V_ADID LOOP
RETURN NEXT v_rec;
END LOOP;
ELSE
FOR v_rec IN SELECT v_Ver as Ver ,'' as Url ,'' as LinkUrl LOOP
RETURN NEXT v_rec;
END LOOP;
END IF;
END;
$$
LANGUAGE PLPGSQL;
postgres=# select * from my_function(2);
ERROR: function my_function(integer) does not exist
LINE 1: select * from my_function(2);
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
因为没有指定返回的具体类型,所以报错了,指定类型:
postgres=# select * from my_function2(2) as (Ver int, Url text, Linkurl text);
ver | url | linkurl
-----+----------------+-----------------------
2 | www.google.com | http://www.google.com
(1 row)
|
|