函数不能返回记录的单个项目,是吗?任何解决方法? [英] Functions can not to return individual items of a record, is it? Any workaround?
问题描述
C语言数据类型中 struct 的基本内容 ,存在于所有流行语言中,并且期望这些语言的函数也可以返回 struct ...。并且,根据正交性原理,期望您可以访问返回的 struct但是,PostgreSQL不提供对 FUNCTION的 struct itens 的访问。 ..返回记录
。
但是程序员使用PostgreSQL时不会抱怨... 有一个简单而直观的解决方法吗?
类似的问题: PostgreSQL v9.X具有真实的记录数组?
以典型案例为例
创建函数foo(int)返回记录为$$
选择$ 1作为a,'Hello#'|| $ 1作为b;
$语言SQL;
SELECT foo(6); -有效,但是我只需要一项
在 SQL上下文中访问记录项:
SELECT(foo(6))。a; -不起作用(但没有歧义!)
-对于语法讨论:
WITH f AS(SELECT foo(6)as r)SELECT r.a FROM f; -不起作用
-语法不明确;将r与表格混淆,在 fra中,将f与架构
-也许r ['a']是一个不错的语法解决方案
在 PLpgSQL上下文中访问记录项目:
怎么说 x:=(foo(6))。a
或 y:= foo(6); x:= y.a
?现在在PLpgSQL中有一些预期的行为,至少允许命名记录:
CREATE FUNCTION bar()以$ F $
DECLARE
tmp记录的形式返回文本;
的文字;
开始
-s:=(foo(7))。b; NOT WORKS,就像匿名记录(不允许)
tmp:= foo(6);
s:= tmp.b; - 有用!!就像命名记录(允许)
RETURN s ||’! ’|| tmp.a; -...正在访问其他任何个人,并使其延误
END;
$ F $语言plpgsql不可用;
如果使用更灵活的返回表
,而不是(有些过时的)返回记录
,那么事情就变得很容易了:
CREATE FUNCTION foo(int)RETURNS table(a int,b text)
AS
$
SELECT $ 1 as,'Hello #'|| $ 1 as b;
$语言SQL;
现在您可以使用:
从foo(6)中选择b
;
如果您担心表与记录,还可以定义一个 type
来克服其他结果集定义:
创建类型foo_return为(int ,b文字);
CREATE FUNCTION foo(int)返回foo_return
AS
$
SELECT $ 1, Hello# || $ 1;
$语言SQL;
您仍然可以在上面进行选择:
从foo(6)中选择b
;
第三个可能更像 C的方法是使用参数(如手册中所示)
创建函数foo(p1 int,out a int,out b text)
AS
$$
SELECT $ 1,'Hello#'| | $ 1;
$$
语言SQL;
那么您就不需要来自
:
select(foo(1))。b;
Basic things as struct in C data types, exist in all popular languages, and is expected that functions, of these languages, also can return a struct... And, by an orthogonality principle, is expected you can access the returned struct itens.
PostgreSQL, nevertheless, did not offer access to the struct itens of a FUNCTION ... RETURNS RECORD
. It is correct?
But programmers use PostgreSQL without complaining... There are a simple and intuitive workaround?
Similar question: PostgreSQL v9.X have real "array of record"?
Illustrating by typical cases
CREATE FUNCTION foo(int) RETURNS RECORD AS $$
SELECT $1 as a, 'Hello #'||$1 as b;
$$ LANGUAGE SQL;
SELECT foo(6); -- works, but I need only one item
Access of record itens in a SQL context:
SELECT (foo(6)).a; -- NOT works (but no ambiguity!)
-- For syntax discussion:
WITH f AS (SELECT foo(6) as r) SELECT r.a FROM f; -- NOT works
-- ambiguous syntax; confused r with table, in "f.r.a", f with schema
-- perhaps r['a'] would be a good syntax solution
Access of record itens in a PLpgSQL context:
How to say x:=(foo(6)).a
or y:=foo(6); x:=y.a
? Now there are some expected behaviuor, in PLpgSQL, at least "named record" is permitted:
CREATE FUNCTION bar() RETURNS text AS $F$
DECLARE
tmp record;
s text;
BEGIN
-- s:=(foo(7)).b; NOT WORKS, is like an "anonymous record" (not permitted)
tmp := foo(6);
s:=tmp.b; -- IT WORKS!! is like a "named record" (permitted)
RETURN s||'! '||tmp.a; -- ...works accessing any other individual itens
END;
$F$ LANGUAGE plpgsql IMMUTABLE;
If you use the more flexible returns table
instead of (the somewhat outdated) returns record
, then things get really easy:
CREATE FUNCTION foo(int) RETURNS table (a int, b text)
AS
$$
SELECT $1 as a, 'Hello #'||$1 as b;
$ LANGUAGE SQL;
now you can use:
select b
from foo(6);
If you are concerned about "tables" vs. "records" you can also define a type
to overcome the additional result set definition:
create type foo_return as (a int, b text);
CREATE FUNCTION foo(int) RETURNS foo_return
AS
$$
SELECT $1, 'Hello #'||$1;
$$ LANGUAGE SQL;
You can still the above select then:
select b
from foo(6);
A third maybe more "C" like approach would be to use out parameters (as shown in the manual)
CREATE FUNCTION foo(p1 int, out a int, out b text)
AS
$$
SELECT $1, 'Hello #'||$1;
$$
LANGUAGE SQL;
Then you don't need a from
:
select (foo(1)).b;
这篇关于函数不能返回记录的单个项目,是吗?任何解决方法?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!