Exception in PL/pgSQL function

Question

I have small issue about exception in PL/pgSQL. My task is to write a function to find reservoirs with a certain length.

My code:

create or replace function
 info_about_reservoir(num_of_letters int)
 returns int as
$$
 declare
 res_name varchar(50);
 res_type varchar(50);
 res_area decimal(10,0);
 counter int := 1;
 begin
 select r_name,t_name,r_area into strict res_name,res_type,res_area
 from 
 reservoirs right outer join reservoirs_types 
 on t_id=r_t_id
 where char_length(r_nazwa)=1ドル;
 raise notice 'Name: %, type: %, area: %',res_name,res_type,res_area;
 exception
 when no_data_found then
 raise notice 'No reservoir with name lenght %',1ドル;
 when too_many_rows then
 raise notice 'Too much reservoirs with name lenght %',1ドル;
 return counter;
 end;
$$ language plpgsql;

For num_of_letters must return exceptions: --SELECT info_about_reservoir(7) -- no_data_found --SELECT info_about_reservoir(8) -- too_many_rows --SELECT info_about_reservoir(9) -- Name: % ...

In previous versions of this script I have returned only exceptions and ERROR: query has no destination for result data. Now it returns for 7: Name: ... for 8: Name: first row from some rows query ... for 9: Name: row from one row query ...

---

Sorry for the confusion, I have an answer for this:

create or replace function
 info_about_reservoir(num_of_letters int)
 returns int as
$$
 declare
 res_name varchar(50);
 res_type varchar(50);
 res_area int;
 counter int := 1;
 begin
 select r_name,t_name,r_area into strict res_name,res_type,res_area
 from 
 reservoirs right outer join reservoirs_types 
 on t_id=a_t_id
 where char_length(r_name)=1ドル;
 raise notice 'Name: %, type: %, area: %',res_name,res_type,res_area;
 return counter;
 exception
 when no_data_found then
 raise notice 'No reservoir with name lenght %',1ドル;
 return counter;
 when too_many_rows then
 raise notice 'Too much reservoirs with name lenght %',1ドル;
 return counter;
 end;
$$ language plpgsql;

Now it works. :D

• Filipe Silva: You should add your answer as an actual answer.
• mu is too short: And then you can come back in a day and accept your own answer.
• Erwin Brandstetter: Can you please clarify which column belongs to which table in your code by table-qualifying them? That's good practice to begin with and I suspect there is still a glitch in there ..

Answer 1

^{Building on assumptions about your missing table definitions.}

The RIGHT [OUTER] JOIN in your latest version serves no purpose. Since the condition is on the left table, you could as well use [INNER] JOIN. Do you actually want a LEFT JOIN? So that reservoirs without matching reservoirs_type are still returned?

The STRICT modifier in SELECT INTO only considers whether a single row is returned, it does not react to a missing row in a LEFT JOIN (or individual columns being assigned NULL values.

Could look like:

CREATE OR REPLACE FUNCTION info_about_reservoir(num_of_letters int)
 RETURNS int AS
$func$
DECLARE
 res_name text;
 res_type text;
 res_area int;
 counter int := 1;
BEGIN
 SELECT r_name, t_name, r_area -- no table-qualification for lack of info
 INTO STRICT res_name, res_type, res_area
 FROM reservoirs r
 LEFT JOIN reservoirs_types t ON t_id = a_t_id -- OR JOIN, not RIGHT JOIN
 WHERE length(r_name) = 1ドル;
 RAISE NOTICE 'Name: %, type: %, area: %', res_name, res_type, res_area;
 RETURN counter;
EXCEPTION
 WHEN no_data_found THEN
 RAISE NOTICE 'No reservoir with name length %.', 1ドル;
 RETURN counter;
 WHEN too_many_rows THEN
 RAISE NOTICE 'Too many reservoirs with name length %.', 1ドル;
 RETURN counter;
END
$func$ LANGUAGE plpgsql;

• counter is always 1. What's the purpose?