PostgreSQL 9.3: Return strings from function
14,771
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;
Comments
-
MAK almost 2 years
I have the following function called as
pro()
. From which I want to return the strings by union all of twoselect
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 | |______________________________________|
-
MAK over 9 yearsPanther, Yeah! Got it. Thank you so much.
-
pozs over 9 years
RETURN QUERY EXECUTE
is not necessary, a simpleRETURN QUERY
should be enough, if there are no dynamic query parts -- also note, that such simple result can also be declared asRETURNS SETOF TEXT
, but of course withRETURNS TABLE
a column alias can be specified at function declaration level.