postgresql中这种查询有没有简单的方法把结果返回
想把查询的结果返回,一个是int 型,后两个是varchar类型的值:可不可以用记录类型返回?
if EXISTS(select * from ADInfo where ADID=v_ADID) then
select v_Ver as Ver ,Url, LinkUrl from ADInfo where ADID=V_ADID;
else
BEGIN
SELECT v_Ver as Ver ,'' as Url ,'' as LinkUrl;
END;
end if; 回复 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)
谢谢你,其实我后来做出来了,用的方法就是你写的其中一种,本来里面有三条查询语句的,不过第一个查询语句主要是用来赋值的,所以当时就在那里纠结的!
虽然这个问题已经解决了,不过还是要谢谢你:))
页:
[1]