PostgreSQL 9.3: Return strings from function

Question

I have the following function called as pro(). From which I want to return the strings by union all of two select statements and product output.

Function: pro()

My try:

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;

Calling Function:

select pro();

This returning only the first part of select statement:

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

But the expected result should be:

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

Vivek S. · Accepted Answer · 2015-03-12 11:30:21Z

7

Try use these functions :

using 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;

or

using 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;

edited Mar 12, 2015 at 11:30

answered Jan 23, 2015 at 5:41

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MAK Over a year ago

Panther, Yeah! Got it. Thank you so much.

pozs Over a year ago

RETURN QUERY EXECUTE is not necessary, a simple RETURN QUERY should be enough, if there are no dynamic query parts -- also note, that such simple result can also be declared as RETURNS SETOF TEXT, but of course with RETURNS TABLE a column alias can be specified at function declaration level.

Collectives™ on Stack Overflow

PostgreSQL 9.3: Return strings from function

My try:

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

My try:

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related