PostgreSQL:How to return result of a SELECT statement within a function using PostgreSQL?

Question

Here i have following function, but not getting how to return the result.

create or replace function f1() returns void as  
$body$  
begin    
    SELECT "Fname",  
        "Lname",   
        count("City" = 'A-B' OR NULL) AS "A-B",  
        count("City" = 'C-D' OR NULL) AS "C-D",  
    FROM "Table1"  
    WHERE  "City" in ('A-B','C-D')  
    GROUP BY 1,2  
    ORDER BY 1,2

When you would to get some result from function, then your function MUST not be VOID function. — Pavel Stehule
– Pavel Stehule, Commented Mar 10, 2014 at 8:05

Craig Ringer · Accepted Answer · 2014-03-10 09:15:32Z

2

Change the return type to RETURNS TABLE (fieldname type, fieldname type, ...) and use RETURN QUERY SELECT ....

See the PL/PgSQL docs.

edited Mar 10, 2014 at 9:15

answered Mar 10, 2014 at 7:47

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

2 Comments

Sarfaraz Makandar Over a year ago

Thank you. But the result is showing in a single column.

Craig Ringer Over a year ago

@user3382347 Wild guess: You're using SELECT my_procedure() not SELECT * FROM my_procedure().

Collectives™ on Stack Overflow

PostgreSQL:How to return result of a SELECT statement within a function using PostgreSQL?

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related