Creating a function in Postgresql that does not return composite values
- by celenius
I'm learning how to write functions in Postgresql. I've defined a function called _tmp_myfunction() which takes in an id and returns a table (I also define a table object type 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
id,
cost
FROM
sales
WHERE
id = sales.id;
END;
$$ LANGUAGE plpgsql;
This works fine when I use one id and call it using the following approach:
SELECT * FROM _tmp_myfunction(402);
What I would like to be able to do is to call it, but to use a column of values instead of just one value. However, if I use the following approach I end up with all values of the table 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 construct my query in such a way that I can separate out the results.
