Postgres function returns custom data set

Question

Is there a way to create postgres stored function (using plpgsql to be able to set input parameters) that returns a custom data set?

I've tried to do something like this according to official manual:

CREATE FUNCTION extended_sales(p_itemno int)
RETURNS TABLE(quantity int, total numeric) AS $$
BEGIN
 RETURN QUERY SELECT quantity, quantity * price FROM sales
 WHERE itemno = p_itemno;
END;
$$ LANGUAGE plpgsql;

but result is an array with only one column which contains type (quantity, total), but I need to get two column array with 'quantity' column and 'total' column.

• user330315: You need to use select * from extended_sales(...). Plus you don't need a PL/pgSQL function: sqlfiddle.com/#!12/d5aef/1

Answer 1

At a guess you're running:

SELECT extended_sales(1);

This will return a composite type column. If you want it expanded, you must instead run:

SELECT * FROM extended_sales(1);

Also, as @a_horse_with_no_name notes, a PL/pgSQL function is completely unnecessary here. Presumably this is a simplified example?

In future please include:

• Your PostgreSQL version; and
• The exact SQL you ran and the exact output you got

• user1820686: It definitely works right now. I take into account your recommendations about db version and sql-script in future, thank you!