PostgreSQL 9.3：从函数返回字符串

Question

我有一个名为pro()的函数。我想通过联合两个select语句并生成输出来返回字符串。

函数: pro()

我的尝试:

create or replace function pro()
returns varchar as

$$

declare
    sql varchar;
    q varchar;
begin
    sql := 'SELECT DISTINCT  CAST(COUNT(ProductNumber) as varchar) ||'' - Count of the product Number '' as Descp
        FROM product
        UNION ALL
        SELECT DISTINCT CAST(COUNT(ProductName) AS varchar) || '' - Count of the product Name '' as Descp
        FROM product';

    raise info '%',sql;

    execute sql into q;

    return q;

end;
$$

language plpgsql;

调用函数:

select pro();

仅返回select语句的第一部分：

 ______________________________________
|pro                                   |
|character varying                     |
|______________________________________|
|6 - Count of the product Number       |
|______________________________________|

但是预期的结果应该是：

 ______________________________________
|pro                                   |
|character varying                     |
|______________________________________|
|6 - Count of the product Number       |
|______________________________________|
|6 - Count of the product Name         |
|______________________________________|

- MAK

Answer 1

尝试使用以下函数：

使用plpgsql

create or replace function pro1()returns 
table ( 
       descp text
      )
as
$$
begin
    return QUERY execute (
         'SELECT DISTINCT  CAST(COUNT(product) as varchar) ||'' - Count of the product Number '' as Descp
         FROM product
         UNION ALL
         SELECT DISTINCT CAST(COUNT(productid) AS varchar) || '' - Count of the product Name '' as Descp
         FROM product');
end;
$$
language plpgsql;

或

使用sql

create or replace function pro2() returns table  ( descp text) 
as
$$
  SELECT DISTINCT  CAST(COUNT(product) as varchar) ||' - Count of the product Number ' as Descp
  FROM product
    UNION ALL
  SELECT DISTINCT CAST(COUNT(productid) AS varchar) || ' - Count of the product Name 'as Descp
  FROM product;
$$
language sql;

- Vivek S.

---

猎豹，是的！搞定了。非常感谢你。 - MAK

如果没有动态查询部分，那么RETURN QUERY EXECUTE并不是必需的，一个简单的RETURN QUERY就足够了。此外，请注意，这样简单的结果也可以声明为RETURNS SETOF TEXT，但当然在函数声明级别上使用RETURNS TABLE可以指定列别名。 - pozs