Calling function plpgsql

Question

but I cant manage to call it.

CREATE or replace FUNCTION wyplaty_dla_prac_o_email (threshold_value integer,email_of text)
RETURNS TABLE(f1 int,f2 timestamp)
AS
$$
begin
select amount, payment_date from payment p join customer c on p.customer_id=c.customer_id 
where amount > 1ドル and email =2ドル;
end;
$$
LANGUAGE plpgsql;

and when I call it

select * from wyplaty_dla_prac_o_email(6,'[email protected]')

it says that ERROR: It was not indicated where the query results should be saved

Answer 1

You're missing a return statement. Your function is executing a query but not doing anything with the result, which is producing the error.

You need to return the results of your query like so:

CREATE or replace FUNCTION wyplaty_dla_prac_o_email (threshold_value integer,email_of text)
RETURNS TABLE(f1 int,f2 timestamp)
AS $$ begin
return query 
 select amount, payment_date from payment p join 
 customer c on p.customer_id=c.customer_id 
 where amount > 1ドル and email =2ドル;
end; $$
LANGUAGE plpgsql;

Alternatively you could just specify the function in SQL instead of using plpgsql if it's just a wrapper around a query and save yourself the overhead.

• user330315: Alternatively use language sql and then the return query is no longer necessary
• Johannes H.: I did say that in the last paragraph as well. But yes, that's correct.