CODEWITHSUNDEEP
sqlpostgresql
thomas1413
thomas1413

Reputation: 67

Calling function plpgsql

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

Upvotes: 0

Views: 36

Answers (1)

Johannes H.
Johannes H.

Reputation: 6167

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.

Upvotes: 1

Related Questions