PostgreSQL如果不知道函数的返回类型，怎么处理(自定义结果集)

2008年8月15日 0条评论 728次阅读 0人点赞

create table department(
id int,
name text
);

create table employee(
id int,
name text,
salary int,
departmentid int
);

insert into department values (1, ‘Management’);
insert into department values (2, ‘IT’);

insert into employee values (1, ‘John Smith’, 30000, 1);
insert into employee values (2, ‘Jane Doe’, 50000, 1);
insert into employee values (3, ‘Jack Jackson’, 60000, 2);

—————————————————–
— 创建函数
create or replace function GetRows(text) returns setof record as
$$
declare
r record;
begin
for r in EXECUTE ‘select * from ‘ || $1 loop
return next r;
end loop;
return;
end;
$$
language ‘plpgsql’;

—————————————–
— ******** 调用函数 **********

select * from GetRows(‘Department’)
/* 下面的，如果不知道数据集的列数，和类型，怎么处理 */
as dept(deptid int, deptname text);

[向版主推荐这条信息]



吕不为
Master Sergeant

发贴数: 71
注册时间：
七月 2003
[加入好友列表]
[拒绝这个用户提交的所有信息] 回复：如果不知道函数的返回类型，怎么处理   五, 03 六月 2005 21:48

没有发现有解决的办法，好象只能用
as dept(deptid int, deptname text);
这样的形式。
[向版主推荐这条信息]



laser
Brigadier General

Administrator

发贴数: 5569
地区: 想改美漂
注册时间：
一月 2003
[加入好友列表] 回复：如果不知道函数的返回类型，怎么处理五, 03 六月 2005 21:48

我只是好奇，你为什么会不知道呢？

The universe is neither benign nor hostile, just indifferent

[向版主推荐这条信息]



阿弟
Major

发贴数: 1113
地区: pgsql天堂
注册时间：
八月 2003
[加入好友列表]
[拒绝这个用户提交的所有信息] 回复：如果不知道函数的返回类型，怎么处理   六, 04 六月 2005 09:01

沒有就返回個1或哉0什麼的不就行，不要想得那麼複雜

絕不、絕不、絕不放棄!
php+pgsql8.0開發工票工資系統

Email:chenaisheng@pgsqldb.com

[向版主推荐这条信息]

wyong
Corporal

发贴数: 24
注册时间：
一月 2005
[加入好友列表]
[拒绝这个用户提交的所有信息]
回复：如果不知道函数的返回类型，怎么处理六, 04 六月 2005 09:09

请看如下函数，它是一个交叉表的例子’不良名称’是不固定的，那么返回的结果也不一样，

–交叉表的问题如下:
–1.先建表JIAOCHA_SUM

CREATE TABLE JIAOCHA_SUM (
姓名 varchar(8 ),
产量 numeric(18, 0),
不良总数 numeric(18, 0),
不良名称 varchar (12),
不良数量 numeric(5, 0)
);
–2.再插入9行数据

INSERT INTO JIAOCHA_SUM VALUES (‘王五’,300,30,’不良1′,10);
INSERT INTO JIAOCHA_SUM VALUES (‘王五’,300,30,’不良8′,20);
INSERT INTO JIAOCHA_SUM VALUES (‘王五’,400,18,’不良4′,18);
INSERT INTO JIAOCHA_SUM VALUES (‘李四’,200,24,’不良1′,7);
INSERT INTO JIAOCHA_SUM VALUES (‘李四’,200,24,’不良3′,8);
INSERT INTO JIAOCHA_SUM VALUES (‘李四’,200,24,’不良4′,9);
INSERT INTO JIAOCHA_SUM VALUES (‘张三’,100,11,’不良1′,5);
INSERT INTO JIAOCHA_SUM VALUES (‘张三’,100,11,’不良2′,6);
INSERT INTO JIAOCHA_SUM VALUES (‘张三’,150,1,’不良3′,1);

–3.再运行的结果

CREATE OR REPLACE FUNCTION f_JiaoC() RETURNS SETOF RECORD
AS $$
DECLARE
rec RECORD;
s1 text;
BEGIN
s1 := ‘SELECT 姓名,SUM(产量) AS 产量,SUM(不良总数) AS 不良总数’ || chr(10);
FOR rec IN SELECT 不良名称 FROM JIAOCHA_SUM GROUP BY 不良名称 ORDER BY 不良名称 LOOP
s1 := s1 || ‘, SUM(CASE WHEN 不良名称 = ”’ || rec.不良名称 || ”’ THEN 不良数量 ELSE 0 END) AS ‘ || rec.不良名称 || chr(10);
END LOOP;
s1 := s1 || ‘FROM JIAOCHA_SUM GROUP BY 姓名 ORDER by 姓名’;

FOR rec IN EXECUTE s1 LOOP
RETURN NEXT rec;
END LOOP;

RETURN ;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM f_JiaoC()
/* 下面的返回类型如果不知道，怎么返回数据集 */
AS a(姓名 varchar(20), 产量 numeric(18, 0), 不良总数 numeric(18, 0)
,不良1 numeric(5, 0), 不良2 numeric(5, 0), 不良3 numeric(5, 0)
,不良4 numeric(5, 0), 不良8 numeric(5, 0));

DROP TABLE JIAOCHA_SUM;

DROP FUNCTION f_JiaoC();

[向版主推荐这条信息]

吕不为
Master Sergeant

发贴数: 71
注册时间：
七月 2003
[加入好友列表]
[拒绝这个用户提交的所有信息] 回复：如果不知道函数的返回类型，怎么处理六, 04 六月 2005 10:02

这让我想到一个相似的问题，可能和wyong的是一个问题，不同的问法吧：
就是：
我希望客户端只用一个 select * from myfunc(mytext)这个函数，就返回
一个数据集，这个数据集到底有几列，有哪些数据，是由myfunc函数实现的。
客户端只需要完整的显示出来即可。不用管其它的。
这种需求如果在SQL SERVER或者interbase就可以很好的处理。
意思是：有可能在这个函数中，一次是
select * from table1
后来由于需求变了，需要显示table2的内容，这时，我只要在函数中改成
select * from tabl2就可以了。客户端程序根本不需要改。
但是如果需要像PG一样定义返回的列定义，那么客户端程序就很麻烦了。

如果客户端是编译的，还要重新编译再分发。是有些不方便。

[向版主推荐这条信息]

laser
Brigadier General

Administrator

发贴数: 5569
地区: 想改美漂
注册时间：
一月 2003
[加入好友列表] 回复：如果不知道函数的返回类型，怎么处理一, 06 六月 2005 00:39

吕不为引用 2005-06-04 10:02

这让我想到一个相似的问题，可能和wyong的是一个问题，不同的问法吧：
就是：
我希望客户端只用一个 select * from myfunc(mytext)这个函数，就返回
一个数据集，这个数据集到底有几列，有哪些数据，是由myfunc函数实现的。
客户端只需要完整的显示出来即可。不用管其它的。
这种需求如果在SQL SERVER或者interbase就可以很好的处理。
意思是：有可能在这个函数中，一次是
select * from table1
后来由于需求变了，需要显示table2的内容，这时，我只要在函数中改成
select * from tabl2就可以了。客户端程序根本不需要改。
但是如果需要像PG一样定义返回的列定义，那么客户端程序就很麻烦了。

如果客户端是编译的，还要重新编译再分发。是有些不方便。

我觉得奇怪，SQL SERVER和INTERBASE怎么能知道你的myfunc()函数返回
的什么东西？如果你的myfunc()返回的结果集的格式会发生变化，你的
客户端怎么可能不修改呢？
这个概念就像：
我又两个表 table1 和 table2，两个表的结构不同，而你的客户端在
select * from table1;
改为
select * from table2;
的时候竟然完全不需要变化？你是这个意思么？
如果是这个情况，那么实际上你看到的东西是不一样的，
因为table1和table2的结构实际上是已知的，而PG的
select * from myfunc() as (…);
这种方式，根本就是对myfunc()返回的表结构没有任何定义，
所以我才会问，你为什么会不知道它的结构？

The universe is neither benign nor hostile, just indifferent

[向版主推荐这条信息]

吕不为
Master Sergeant

发贴数: 71
注册时间：
七月 2003
[加入好友列表]
[拒绝这个用户提交的所有信息] 回复：如果不知道函数的返回类型，怎么处理一, 06 六月 2005 19:57

多谢laser的回复。
是这样的，经常有时候，客户端用一个类似Grid的控件或功能。第一列的标题是字段名，第一行是字段的值，就象我们常见的pgadmin3中的显示数据的那种格式；这种情况下，我是不需要知道有几个字段的。有几个就显示几个嘛。比如
我有一个人员表，现在我要加一个“地址2”字段，那么客户端只需要用
select * from myfunc(‘2002’)就行了，有几个字段就都显示出来。
SQLSERVER和INTERBASE是这样实现的
>>>>>>>>>>>>>>>>>>>>>>>>>>>>
create table table1(bh char(20),bhname char(20));
insert into table1 values(‘001′,’100’);
insert into table1 values(‘002′,’200’);

create table table2(mybh char(20),mybhname char(20),mydesc char(20));
insert into table2 values(‘A01′,’A10′,’01A’);
insert into table2 values(‘A02′,’A20′,’02A’);

create function b1()returns table as
return
select * from table1

select * from b1()

alter function b1()returns table as
return
select * from table2

select * from b1()

这时，你会看到客户端只需要用一条select * from b1(),就可以得到
不同表的不同的内容，客户端根本不需要知道有几列，有几列就显示几列嘛。
不知道PG该如何实现。interbase基本也是这样的语法结构。

[向版主推荐这条信息]

laser
Brigadier General

Administrator

发贴数: 5569
地区: 想改美漂
注册时间：
一月 2003
[加入好友列表] 回复：如果不知道函数的返回类型，怎么处理一, 06 六月 2005 21:46

其实，你的例子说明，实际上客户端还是要去拿元数据的，
table1和table2都是已知的，其结构存储在系统表里，所以，
这样的情况，仍然算表结构已知，b1() 只是 select table1 union select table2 而已。

实际上postgresql支持完全动态的表结构定义：
laser=# create or replace function rec_ret() returns setof record as
$$
declare rec record;
begin
select into rec ‘1’::integer, ‘hello, world!’::text, ‘123.456’::numeric(12,3);
return next rec;
return;
end;
$$language plpgsql ;

CREATE FUNCTION

laser=# select * from rec_ret() as(f1 integer, f2 text, f3 numeric(12,3)); f1 | f2 | f3
—-+—————+———
1 | hello, world! | 123.456
(1 行)

这里的“表”实际上是不存在于系统表里面的，因此，需要我们制定
结构，也就是说，PG会动态地根据你制定的表结构，来变化需要生成
的东西，你的输出数据，完全可以根据需要变化。但是，不管怎么说，
这个时候客户端，或者说用户应用，也是应该知道输出数据的样子的，
我不相信 SQL SERVER 或者 INTERBASE 可以做到真正不知道输出数据
的格式（不是你不知道，而是客户端也不知道）；上面的例子里，
实际上psql是客户端，它是知道表的样子的，通过as子句知道，当然，
其实这也是后台告诉它的，但是，这样，客户端才能显示，否则根本
不可能显示。

而你在其它的帖子里用CREATE TYPE做的例子，实际上和CREATE TABLE
是一样的，在PG里，CREATE TYPE很早以前就是CREATE TABLE的别名。
所以，你创建了类型，于是系统知道元数据。这样还属于比较简单的，
真正复杂的就是上面返回RECORD的例子，因为，你的应用完全可以在
运行时生成表结构（当然，你的应用自己得知道如何处理了。）这也是
解析语言的好处。

[更新: 二, 07 六月 2005 19:05]

The universe is neither benign nor hostile, just indifferent

[向版主推荐这条信息]

吕不为
Master Sergeant

发贴数: 71
注册时间：
七月 2003
[加入好友列表]
[拒绝这个用户提交的所有信息] 回复：如果不知道函数的返回类型，怎么处理一, 06 六月 2005 23:17

非常感谢laser,通过laser的热心解答，我对这个问题的理解已经很清楚了。
明白了PG是如何动态处理这种问题的。只是自己以前看的太少。
SQLSERVER和INTERBASE其实也是告诉客户端的，只是他是默认隐式告诉。不像普通的PG函数要求显式告诉。

您看：
即然在函数定义的时候已经说了， ‘1’::integer, ‘hello, world!’::text, ‘123.456’::numeric(12,3);
第一个字段的内容是整数，第二个是text,第三个是numberic,那么为什么还必须要客户端指明呢。〈as(f1 integer, f2 text, f3 numeric(12,3)); 〉
如果客户端我写成
select * from rec_ret() as(f1 text, f2 text, f3 numeric(12,3));
就是出现语句错误。即：
我只能以服务端函数定义好的的结构去用它，这样来看。就显的我客户端的定义无意义。因为服务端强制我只能用他定义好的结构。所以
SQLSERVER和INTERBASE采用了不用显式定义而直接返回服务端定义的结构和数据给客户端。
即:客户端会跟服务端函数说：你都给我定义好了，我只能用这样的结构，你还要我再定义一次，你不是多此一举吗？你返回的时候直接返回我能用的类型和数据就行了嘛。
————————-
请laser指正。
[更新: 一, 06 六月 2005 23:43]

[向版主推荐这条信息]

wyong
Corporal

发贴数: 24
注册时间：
一月 2005
[加入好友列表]
[拒绝这个用户提交的所有信息]
回复：如果不知道函数的返回类型，怎么处理二, 07 六月 2005 11:06

看了laser回复，可能是我的问题有些模糊，
其实对于函数的返回类型，我们是知道的，就象我上贴发的例子一样，
虽然新的交叉表的列是由原表的行生成的，但我们也可以知道原来它是
什么数据类型的数据类型已经知道，但它到底会返回多少列呢？我们无法固定，
既然无法固定，那么也不可能返回数据集，也就无法生成一些动态表。

附件： 1.JPG
(大小: 20.48KB, 下载次数144)
[向版主推荐这条信息]

laser
Brigadier General

Administrator

发贴数: 5569
地区: 想改美漂
注册时间：
一月 2003
[加入好友列表] 回复：如果不知道函数的返回类型，怎么处理二, 07 六月 2005 18:34

wyong 引用 2005-06-07 11:06

你这个记录其实是OUTER JOIN的问题，如果你JOIN的时候加上OUTER
子句，那么这个时候即使李四的记录被删除，张三也会有BBB字段列出，
只是值为空，也就是null，在psql里select出来是啥也没有。

The universe is neither benign nor hostile, just indifferent

[向版主推荐这条信息]

laser
Brigadier General

Administrator

发贴数: 5569
地区: 想改美漂
注册时间：
一月 2003
[加入好友列表] 回复：如果不知道函数的返回类型，怎么处理   二, 07 六月 2005 19:59

吕不为引用 2005-06-06 23:17
您看：
即然在函数定义的时候已经说了， ‘1’::integer, ‘hello, world!’::text, ‘123.456’::numeric(12,3);
第一个字段的内容是整数，第二个是text,第三个是numberic,那么为什么还必须要客户端指明呢。〈as(f1 integer, f2 text, f3 numeric(12,3)); 〉
如果客户端我写成
select * from rec_ret() as(f1 text, f2 text, f3 numeric(12,3));
就是出现语句错误。即：
我只能以服务端函数定义好的的结构去用它，这样来看。就显的我客户端的定义无意义。因为服务端强制我只能用他定义好的结构。所以
SQLSERVER和INTERBASE采用了不用显式定义而直接返回服务端定义的结构和
数据给客户端。
即:客户端会跟服务端函数说：你都给我定义好了，我只能用这样的结构，你还要我再定义一次，你不是多此一举吗？你返回的时候直接返回我能用的
类型和数据就行了嘛。
————————-
请laser指正。

你说的这个问题，原因是因为PG的类型系统更加灵活，比如：
create or replace function rec_ret(i int) returns setof record as $$
declare
rec record;
begin
        if i > 10 then
        select into rec 1, 2, 12.5;
        return next rec;
        else
        select into rec 1, ‘hello, world!’::text, ‘123.456’::numeric(12,3);
        return next rec;
        end if;
        return;
end;$$ language plpgsql;

laser=# select * from rec_ret(1) as (f1 int, f2 text, f3 numeric);
f1 | f2 | f3
—-+—————+———
1 | hello, world! | 123.456
(1 行)

laser=# select * from rec_ret(100) as (f1 int, f2 int, f3 numeric);
f1 | f2 | f3
—-+—-+——
1 | 2 | 12.5
(1 行)

这个意思是说，你同样一个函数，返回的完全可以不同。
至于为什么有些需要做类型转换，有些不用，是因为PG在分析plpgsql
的代码的时候，用的是SQL的主分析器，（为了精简代码）因此，
需要SQL类型提示，这个是一个TODO，将来可能就不需要了。

（因为涉及类型方面的问题，所以就不铺开了，慢慢聊吧。PG
的类型系统很强。）
[更新: 二, 07 六月 2005 20:02]

The universe is neither benign nor hostile, just indifferent

[向版主推荐这条信息]

laser
Brigadier General

Administrator

发贴数: 5569
地区: 想改美漂
注册时间：
一月 2003
[加入好友列表] 回复：如果不知道函数的返回类型，怎么处理二, 07 六月 2005 20:41

再举个例子：

create or replace function funcTable(tablename text) returns setof record as $$
declare
r record;
begin
        for r in EXECUTE ‘select * from ‘ || tablename loop
                return next r;
        end loop;
        return;
end;
$$ language plpgsql;

laser=# select * from funcTable(‘a’) as (f1 integer, f2 integer, f3 integer);
f1 | f2 | f3
—-+—-+—-
1 | 1 | 1
(1 行)

laser=# select * from a;
a | b | c
—+—+—
1 | 1 | 1
(1 行)

record 相当于一个占位符，需要你告诉他表的样子。
就是这个意思。

The universe is neither benign nor hostile, just indifferent

[向版主推荐这条信息]



jhbfx
Corporal

发贴数: 26
注册时间：
五月 2005
[加入好友列表]
[拒绝这个用户提交的所有信息] 回复：如果不知道函数的返回类型，怎么处理   三, 08 六月 2005 13:35

明白了。
多谢！
[向版主推荐这条信息]



11122233
Sergeant

发贴数: 39
注册时间：
九月 2003
[加入好友列表]
[拒绝这个用户提交的所有信息] 回复：如果不知道函数的返回类型，怎么处理   五, 10 六月 2005 13:22

作个标记先。我也有这样的问题。
[向版主推荐这条信息]



jhbfx
Corporal

发贴数: 26
注册时间：
五月 2005
[加入好友列表]
[拒绝这个用户提交的所有信息] 回复：如果不知道函数的返回类型，怎么处理   五, 10 六月 2005 16:02

我试了一下，将函数的返回值定义为setof record，然后再创建一个新的类型。
在使用函数的时候，用新定义的类型来标记（即使用 as “新类型名”），结果是出现错误：“ERROR: a column definition list is required for functions returning “record””，但直接用as (…),却是可以。

拿前面的例子来说：
select * from funcTable(‘a’) as (f1 integer, f2 integer, f3 integer);是可以运行的。

如果定义一个新类型：
CREATE TYPE type1 AS (f1 integer, f2 integer, f3 integer);
运行
select * from funcTable(‘a’) as type1；
就出错误了。

这是bug嘛？
[向版主推荐这条信息]

laser
Brigadier General

Administrator

发贴数: 5569
地区: 想改美漂
注册时间：
一月 2003
[加入好友列表] 回复：如果不知道函数的返回类型，怎么处理五, 10 六月 2005 16:33

不算bug吧，因为record实际上相当于一个占位符或者说指针。
也就是说，系统看到record不会反应过来要去查系统表，不过，
这倒是可以作为一个特性（feature），有空看看TODO里有没有。
呵呵。

The universe is neither benign nor hostile, just indifferent

[向版主推荐这条信息]

jhbfx
Corporal

发贴数: 26
注册时间：
五月 2005
[加入好友列表]
[拒绝这个用户提交的所有信息] 回复：如果不知道函数的返回类型，怎么处理五, 10 六月 2005 16:45

有时候，类型比较复杂，使用“as 新类别名”的方式，比直接用类型标记要方便一些。

真希望以后的版本能加上这个功能。

[向版主推荐这条信息]

比格高

PostgreSQL如果不知道函数的返回类型，怎么处理(自定义结果集)

发表评论