Fonction display_address() avec 2 paramètres

display_address_001.sql

--
-- Fonction à 2 paramètres, n'affichera pas le nom de la société car cet arguemnt en python est à False
--
CREATE OR REPLACE FUNCTION display_address(t_country_id INTEGER, t_address_id INTEGER)
  RETURNS TEXT AS
$BODY$

DECLARE

    r RECORD;
    addr_format TEXT;
    v_parent_id INTEGER;
    company VARCHAR(64);
BEGIN

  SELECT part.use_parent_address, part.name, part.street, part.street2, part.zip, part.city, 
       part.parent_id, ctry.code as country_code, ctry.name as country_name, 
       state.code as state_code, state.name as state_name
  INTO r
  FROM res_partner part
  LEFT JOIN res_country ctry on ctry.id = part.country_id
  LEFT JOIN res_country_state state on state.id = part.state_id
  WHERE part.id=t_address_id;

  v_parent_id := r.parent_id;

  IF r.use_parent_address = true THEN
	SELECT part.use_parent_address, part.name, part.street, part.street2, part.zip, part.city, 
       part.parent_id, ctry.code as country_code, ctry.name as country_name, 
       state.code as state_code, state.name as state_name
	  INTO r
	  FROM res_partner part
	  LEFT JOIN res_country ctry on ctry.id = part.country_id
	  LEFT JOIN res_country_state state on state.id = part.state_id
	  WHERE part.id=r.parent_id;
  END IF;


  SELECT address_format INTO STRICT addr_format FROM res_country where id=t_country_id;

  IF v_parent_id IS NOT NULL THEN
     SELECT name INTO STRICT company FROM res_partner WHERE id = v_parent_id;
     addr_format := company||chr(10)||addr_format;
  END IF;
  
  addr_format := replace(addr_format, '%(street)s', COALESCE(r.street, ''));

  addr_format := replace(addr_format, '%(street2)s', COALESCE(r.street2, ''));
  addr_format := replace(addr_format, '%(zip)s', COALESCE(r.zip, ''));
  addr_format := replace(addr_format, '%(city)s', COALESCE(r.city, ''));
  -- state data
  addr_format := replace(addr_format, '%(state_code)s', COALESCE(r.state_code, ''));
  addr_format := replace(addr_format, '%(state_name)s', COALESCE(r.state_name, ''));
  -- country data
  addr_format := replace(addr_format, '%(country_code)s', COALESCE(r.country_code, ''));
  addr_format := replace(addr_format, '%(country_name)s', COALESCE(r.country_name, ''));
  -- Supression des saut de lignes
  addr_format := replace(addr_format, chr(10)||chr(10), chr(10));
  RETURN addr_format;

END;

$BODY$
LANGUAGE plpgsql IMMUTABLE;