PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement

How are you executing that function? It works as a select statement.

Create a table: public.users

create table public.users (id int, firstname varchar, lastname varchar);

Insert some records:

insert into public.users values (1, 'aaa','bbb'),(2,'ccc','ddd');

function: my_function

CREATE OR REPLACE FUNCTION my_function(user_id integer) RETURNS TABLE(id integer, firstname character varying, lastname character varying) AS $$    DECLARE        ids INTEGER[];    BEGIN         ids := ARRAY[1,2];         RETURN QUERY             SELECT users.id, users.firstname, users.lastname             FROM public.users             WHERE users.id = ANY(ids);    END;$$ LANGUAGE plpgsql;

Now you can use with *

select * from my_function(1);

Result of query

 id | firstname | lastname ----+-----------+----------  1 | aaa       | bbb  2 | ccc       | ddd

Or with column names as well

select id,firstname,lastname from my_function(1);

Result

 id | firstname | lastname ----+-----------+----------  1 | aaa       | bbb  2 | ccc       | ddd

Call function like that :

select * from  my_function(123);

Not just with select. I did and It works

http://www.postgresqltutorial.com/plpgsql-function-returns-a-table/

there is a difference in the output received from the function depending on the syntax of the selection:

select * from myfunction();

and

select myfunction();