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

Question

I've got this PL/pgSQL function which must return some users information.

CREATE OR REPLACE FUNCTION my_function(         user_id integer     ) RETURNS TABLE(             id integer,              firstname character varying,             lastname  character varying         ) AS $$     DECLARE         ids character varying;     BEGIN         ids := '';         --Some code which build the ids string, not interesting for this issue         RETURN QUERY              EXECUTE 'SELECT                          users.id,                          users.firstname,                          users.lastname                     FROM public.users                      WHERE ids IN (' || ids || ')';     END; $$ LANGUAGE plpgsql; 

The problem I'm facing is that the result of the function is a single columns table like this:

╔═══╦═════════════════════╗ ║   ║my_function          ║ ╠═══╬═════════════════════╣ ║ 1 ║ (106,Ned,STARK)     ║ ║ 2 ║ (130,Rob,STARK)     ║ ╚═══╩═════════════════════╝ 

While I expected:

╔═══╦════════════╦════════════╦═════════════╗ ║   ║ id         ║ firstname  ║ lastname    ║ ╠═══╬════════════╬════════════╬═════════════╣ ║ 1 ║ 106        ║ Ned        ║ STARK       ║ ║ 2 ║ 103        ║ Rob        ║ STARK       ║ ╚═══╩════════════╩════════════╩═════════════╝ 

I think (but not sure) the problem comes from the EXECUTE statement, but I can't see how to do otherwise.

Any ideas?

Answer 1

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 

Answer 2

Call function like that :

select * from  my_function(123); 

Not just with select. I did and It works