CODEWITHSUNDEEP
stored-proceduresplpgsqlpostgresql-9.2
user1820686
user1820686

Reputation: 2117

Postgres function returns custom data set

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.

Upvotes: 0

Views: 2381

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324365

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

Upvotes: 2

Related Questions