User defined function only returns first value in column

Question

I'm writing a function for a class that is supposed to transform one the the columns in my table. I'm using a database they gave me (dvd rental). The end goal, I think is to add this function as a substitute for a column name inside another query. (I'm only guessing because the instructions for this class are super vague and the teacher has been no help so far)

CREATE OR REPLACE FUNCTION rental_amount()
RETURNS text AS $dollar_amount$
DECLARE dollar_amount text;
BEGIN
SELECT TO_CHAR(amount,'l9D99') amount_format INTO dollar_amount
FROM detail_report
ORDER BY title;
RETURN dollar_amount;
END; $dollar_amount$
LANGUAGE plpgsql;

What my function does (or what I'm trying to do)is basically append a dollar sign before the numbers in the amount column. When the function is called it only returns the first value from the first row.

When I run the select query inside the function, however, I get all the values. your textenter image description here

How can I fix this?

At first I tried to make the function return a table, but then they added on the additional requirement that I have to include the function within another query. I've tried writing this many different ways but I don't have much of any experience with postgres and the faculty does not seem to want to help. When you ask any questions, they only say refer to xyz or the rubric which doesn't help.

Answer 1

From what you described, seems like the function should only be formatting a value.

create or replace function format_as_money(m float) returns text
    language sql
    immutable
    returns null on null input
    return to_char(1, 'L') || m;

Note, I don't know the specifics of your table or your assignment, but you should be able to adjust from here. See the Examples section of the create function docs.

Then use that in a select by passing the amount column in.

select
  title,
  format_as_money(amount) as formatted_amount
from detail_report
order by title