从“select语句”格式返回函数的结果 [英] Returning results from a function in 'select statement' format

问题描述

 创建或替换函数mffcu.test_ty_hey（）
 RETURNS setof记录
 LANGUAGE plpgsql 
 AS $函数$ 
声明
 cname1文本; 
 sql2 text; 
从$ b $开始在
中选择array_to_string（useme，'，'）从$ b $选择array_agg（column_name）作为用法
从（
）select column_name :: text 
 FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE TABLE_NAME ='crosstab_183'
和ordinal_position！= 1 
）as fin 
）as fine 
 loop 
 sql2：='select distinct array ['|| cname1 ||'] from mffcu.crosstab_183'; 
执行sql2; 
结束循环; 
 END; 
 $ function $ 
  

我用这个函数调用函数：

  select mffcu.test_ty_hey（）
  

如何在没有创建表/临时表的情况下返回 sql2 查询的结果？

 
 >  CREATE OR REPLACE FUNCTION mffcu.test_ty_hey（）
 RETURNS SETOF text []语言plpgsql 
 AS $ func $ 
 DECLARE 
 cname1 text; 
 BEGIN 
 
对于cname1 IN 
 SELECT column_name :: text 
 FROM information_schema.columns 
 WHERE table_name ='crosstab_183'
 AND table_schema = 'mffcu'
 AND ordinal_position<> 1 
 LOOP 
 RETURN QUERY 
 EXECUTE格式（'SELECT DISTINCT ARRAY [％I :: text] 
 FROM mffcu.crosstab_183'，cname1）; 
 END LOOP; 
 
 END 
 $ func 
  

format（） 需要PostgreSQL 9.1或更高版本。在9.0中，您可以用以下替代：

  EXECUTE'SELECT DISTINCT ARRAY ['|| quote_ident（cname1）||':: text] 
 FROM mffcu.crosstab_183'; 
  

通话：

  select  * FROM  mffcu.test_ty_hey（）;  

到 text 我们得到一个一致的数据类型，可以用来声明 RETURN 类型。这种妥协必须从一个函数返回各种数据类型。每种数据类型都可以转换为 text ，所以这是明显的共同点。

顺便说一句，我很难想象围绕每一个值的 ARRAY 包装应该是有益的。我想你可以放弃。

I have a function that looks like this:

CREATE OR REPLACE FUNCTION mffcu.test_ty_hey()
 RETURNS setof record
 LANGUAGE plpgsql
AS $function$
Declare
       cname1 text;   
       sql2 text;      
Begin 
for cname1 in 
select array_to_string(useme, ', ') from (
select array_agg(column_name) as useme
from(
select column_name::text
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'crosstab_183' 
and ordinal_position != 1
) as fin
) as fine
loop  
sql2 := 'select distinct array['|| cname1 ||'] from mffcu.crosstab_183';
execute sql2;
end loop;
END;
$function$

I call the function with this:

select mffcu.test_ty_hey()

How do I return the results of the sql2 query without creating a table/temporary table?

解决方案

While @Pavel is right, of course, your very convoluted function could be untangled to:

CREATE OR REPLACE FUNCTION mffcu.test_ty_hey()
  RETURNS SETOF text[] LANGUAGE plpgsql
AS $func$
DECLARE
    cname1 text;   
BEGIN 

FOR cname1 IN 
    SELECT column_name::text
    FROM   information_schema.columns
    WHERE  table_name = 'crosstab_183' 
    AND    table_schema = 'mffcu' 
    AND    ordinal_position <> 1
LOOP
    RETURN QUERY
    EXECUTE format('SELECT DISTINCT ARRAY[%I::text]
                    FROM   mffcu.crosstab_183', cname1);
END LOOP;

END
$func$

format() requires PostgreSQL 9.1 or later. In 9.0 you can substitute with:

EXECUTE 'SELECT DISTINCT ARRAY['|| quote_ident(cname1) ||'::text]
         FROM   mffcu.crosstab_183';

Call:

select * FROM mffcu.test_ty_hey();

By casting each column to text we arrive at a consistent data type that can be used to declare the RETURN type. This compromise has to be made to return various data types from one function. Every data type can be cast to text, so that's the obvious common ground.

BTW, I have trouble imagining what the ARRAY wrapper around every single value should be good for. I suppose you could just drop that.

这篇关于从“select语句”格式返回函数的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！