how do I get the result of a select within a function in postgresql?-CodePudding

I want to get a table with value 1 as result, but this query returns 0 rows. Why is this happening?

CREATE OR REPLACE FUNCTION test_6()
 RETURNS TABLE
            (
                r int
            )
LANGUAGE plpgsql
AS $function$
BEGIN
   execute 'SELECT 1 as r';
end;
$function$;

select test_6()

CodePudding user response：

You need to add RETURN QUERY

CREATE OR REPLACE FUNCTION test_6()
 RETURNS TABLE
            (
                r int
            )
LANGUAGE plpgsql
AS $function$
BEGIN
   RETURN QUERY
    execute 'SELECT 1 as r';
end;
$function$;

Also, as was suggested in comments bellow, you should use the function like a table select * from test_6()

And you can use just

RETURN QUERY
SELECT 1 as r;