Returning results from a function in 'select statement' format

Question

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?

@ErwinBrandstetter it is psql version 9.1, server version 9.0. — precose, May 21 '13 at 18:34

Erwin Brandstetter · Accepted Answer · 2013-05-21T19:10:33.450

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.

This code does nothing, you have to use RETURN QUERY EXECUTE. Result of only EXECUTE is not forwarded to output. — Pavel Stehule, May 21 '13 at 19:07
I'm getting this error when adding the RETURN QUERY: RETURN cannot have a parameter in function returning set; use RETURN NEXT at or near "QUERY" — precose, May 21 '13 at 19:11
@precose: It should work now, I fixed two oversights and tested. — Erwin Brandstetter, May 21 '13 at 19:16

score 3 · Answer 2 · answered May 21 '13 at 18:23

PostgreSQL functions should to have fixed result type before execution. You cannot specify type late in execution. There is only two workarounds - using temp tables or using cursors.

PLpgSQL language is not good for too generic routines - it good for implementation strict and clean business rules. And bad for generic crosstab calculations or generic auditing or similar generic task. It works, but code is slower and usually not well maintainable.

but reply for your query, you can use a output cursors

example http://okbob.blogspot.cz/2008/08/using-cursors-for-generating-cross.html

Returning results from a function in 'select statement' format

2 Answers2

Linked