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

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()
postgresql


Solution 1:[1]

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;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions