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

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=# \d a
       表 “public.a”
 字段名 |  类型   | 修饰词
——–+———+——–
 a      | integer |
 b      | integer |
 c      | integer |

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 新类别名”的方式,比直接用类型标记要方便一些。

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

[向版主推荐这条信息]



发表评论

电子邮件地址不会被公开。 必填项已用*标注

+ 53 = 61