Creating a function in Postgresql that does not return compound values

Question

I am learning how to write functions in Postgresql. I defined a function called _tmp_myfunction() that takes id and returns a table (I also define the type of the table object called _tmp_mytable )

 -- create object type to be returned CREATE TYPE _tmp_mytable AS ( id integer, cost double precision ); -- create function which returns query CREATE OR REPLACE FUNCTION _tmp_myfunction( id integer ) RETURNS SETOF _tmp_mytable AS $$ BEGIN RETURN QUERY SELECT sales.gid, cost FROM sales WHERE id = sales.gid; END; $$ LANGUAGE plpgsql; 

This works fine when I use a single id and call it using the following approach:

 SELECT * FROM _tmp_myfunction(402); 

What I would like to do is name it, but use a column of values ​​instead of a single value. However, if I use the following approach, I get all the table values ​​in one column, separated by commas:

 -- call function using all values in a column SELECT _tmp_myfunction(t.id) FROM transactions as t; 

I understand that I can get the same result if I use SELECT _tmp_myfunction(402); instead of SELECT * FROM _tmp_myfunction(402); but I don’t know how to build my function in such a way that I don’t get composite values ​​when I pass the column of values.

Answer 1

You can write something like this:

 SELECT (t2.function_row).id, (t2.function_row).cost FROM (SELECT _tmp_myfunction(t.id) as function_row FROM transactions t ) t2; 

It will give you fields instead of compound lines.