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?

Kiddush

Are you calling function with SELECT my_function(123); or SELECT FROM my_function(123); ? – Endoblast

You're totally right, i executed the function with SELECT my_function(123); it works perfectly with the FROM...Thanks you! – Kiddush

How did you put there those tables? – Dredi

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

Stambul

Indeed, i called my function with SELECT my_function(123). Thanks. – Kiddush

Is there any way to solve this --> query returns * instead of specific column name/types – Viipuri

This throws an error: ERROR: syntax error at end of input. It's compiling about "$$" – Elwandaelwee

Answer 2

Call function like that :

select * from  my_function(123);

Not just with select. I did and It works

Iggie

Answer 3

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

Hrutkay