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.

You need to use select * from extended_sales(...). Plus you don't need a PL/pgSQL function: sqlfiddle.com/#!12/d5aef/1 — user330315
– user330315, Commented Dec 13, 2014 at 7:47

Craig Ringer · Accepted Answer · 2014-12-13 11:18:41Z

2

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

answered Dec 13, 2014 at 11:18

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1820686 Over a year ago

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

Collectives™ on Stack Overflow

Postgres function returns custom data set

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related