POSTGRESQL- Query has no destination for result data

Question

I'm new to postgres and to programming and I already searched for solution for this but I couldn't quite get it. I'm trying to make a function that will return information about all the customers on that particular country whenever I call the country. This is the error that pops up. I'm really sorry for asking this but I've been stuck here since yesterday.

ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function country(text) line 5 at SQL statement

Here is the function:

create or replace function country(text) returns text as $$
begin


  select customer_id, customer.first_name, customer.last_name 

  from customer 

  inner join address on customer.address_id = address.address_id 
  inner join city on address.city_id = city.city_id 
  inner join country on city.country_id = country.country_id 

  where country = '$1';
  end;
  $$

  language plpgsql;

Answer 1

If you are executing a select statement in a PL/pgSQL function, then you should place the result of the query in some variable(s) (= the destination). Then you work with the variable(s) in the function. You should also have a RETURN statement.

create or replace function country(text) returns text as $$
declare                   -- declare some variables
  id integer;
  fname text;
  lname text;
begin
  select customer_id, customer.first_name, customer.last_name 
    into id, fname, lname -- store query results in variables
  from customer 
  inner join address on customer.address_id = address.address_id 
  inner join city on address.city_id = city.city_id 
  inner join country on city.country_id = country.country_id 
  where country = $1;     -- don't quote parameter references

  -- do something with the variables and return a value from the function
  return format('%s: %s %s', id, upper(lname), fname);
end;
$$ language plpgsql;

Do note that the above only works if the query returns a single row. If the query returns multiple rows you can use a loop in the function. Even simpler, you can just return the results from the query like so:

create or replace function country(text)
returns table (id integer, first_name varchar, last_name varchar) as $$
begin
  return query
    select customer_id, customer.first_name, customer.last_name 
    from customer 
    inner join address on customer.address_id = address.address_id 
    inner join city on address.city_id = city.city_id 
    inner join country on city.country_id = country.country_id 
    where country = $1;
end;
$$ language plpgsql;

But like Evan Carroll said, unless you need a PL/pgSQL function to modify the data before returning it, you are better off with a simple view.