Forums PostgreSQL.fr

baradji

Membre

fonction avec if imbriqués affichant un select

Bonjour , je m'embrouille depuis de matin avec ma fonction, je souhaite afficher une selection selon des conditions, mais j'ai une erreur :
ERROR:  syntax error at end of input
LINE 54: $BODY$

la fonction fonctionne bien sans l'instruction apres else if.
voici la fonction

CREATE OR REPLACE FUNCTION bis_ca_prog_get_adress(
id integer, postal_address_street character varying,postal_address_city character varying,postal_address_state character varying, postal_address_postcode character varying,
main_address_street character varying,main_address_city character varying,main_address_state character varying, main_address_postcode character varying)
RETURNS SETOF address_ind_org_type AS
$BODY$
DECLARE
mon_record address_ind_org_type;
id ALIAS FOR $1;
BEGIN
  if (select contact_organisation_stk_stakeholder_id from ca_programme where ca_programme_id = id) is not null then
        if (postal_address_street !='' or postal_address_city !='' or postal_address_state !='' or postal_address_postcode !='') then
            FOR mon_record IN
        SELECT case when so2.postal_address_street != '' then so2.postal_address_street else '[INSERT THE STREET AND NUMBER]' end as address_street,
               case when so2.postal_address_city != '' then so2.postal_address_city else '[INSERT THE CITY/SUBURB]' end as address_city,
               case when so2.postal_address_state != '' then so2.postal_address_state else '[INSERT THE STATE]' end as address_state,
               case when so2.postal_address_postcode != '' then so2.postal_address_postcode else '[INSERT THE POSTAL CODE]' end as address_postcode
        FROM ca_programme p
        LEFT JOIN catalog_table fs ON p.ca_cat_fund = fs.catalog_table_id
        LEFT JOIN stk_stakeholder ss2 ON ss2.stk_stakeholder_id = p.contact_stk_stakeholder_id
        LEFT JOIN ind_individual i2 ON ss2.individual_id = i2.individual_id
        LEFT JOIN vw_stk_stakeholder vs2 ON vs2.stk_stakeholder_id = p.contact_organisation_stk_stakeholder_id
        LEFT JOIN stk_organisation so2 ON vs2.stk_organisation_id = so2.stk_organisation_id
        left join (
          SELECT * FROM cat_currency WHERE currency_country = (SELECT param_value FROM cat_global_params WHERE param_name = 'Country')
        ) bar on true
        WHERE ca_programme_id = id   
                loop
             return next mon_record;
                end loop;

            else if (main_address_street !='' or main_address_city !='' or main_address_state !='' or main_address_postcode !='') then
            FOR mon_record IN
        SELECT case when so2.main_address_street != '' then so2.main_address_street else '[INSERT THE STREET AND NUMBER]' end as address_street,
               case when so2.main_address_city != '' then so2.main_address_city else '[INSERT THE CITY/SUBURB]' end as address_city,
               case when so2.main_address_state != '' then so2.main_address_state else '[INSERT THE STATE]' end as address_state,
               case when so2.main_address_postcode != '' then so2.main_address_postcode else '[INSERT THE POSTAL CODE]' end as address_postcode
        FROM ca_programme p
        LEFT JOIN catalog_table fs ON p.ca_cat_fund = fs.catalog_table_id
        LEFT JOIN stk_stakeholder ss2 ON ss2.stk_stakeholder_id = p.contact_stk_stakeholder_id
        LEFT JOIN ind_individual i2 ON ss2.individual_id = i2.individual_id
        LEFT JOIN vw_stk_stakeholder vs2 ON vs2.stk_stakeholder_id = p.contact_organisation_stk_stakeholder_id
        LEFT JOIN stk_organisation so2 ON vs2.stk_organisation_id = so2.stk_organisation_id
        left join (
          SELECT * FROM cat_currency WHERE currency_country = (SELECT param_value FROM cat_global_params WHERE param_name = 'Country')
        ) bar on true
        WHERE ca_programme_id = id   
                loop
             return next mon_record;
                end loop;
          end if;
       end if;
  return;
END
$BODY$
LANGUAGE plpgsql STABLE;
ALTER FUNCTION bis_ca_prog_get_adress(
id integer, postal_address_street character varying,postal_address_city character varying,postal_address_state character varying, postal_address_postcode character varying,
main_address_street character varying,main_address_city character varying,main_address_state character varying, main_address_postcode character varying)
  OWNER TO postgres;

merci pour votre aide

---

www.marchegouro.com

baradji

Membre

Re : fonction avec if imbriqués affichant un select

salut, ca marche, la requete n'est pas trop optimisé, mais ca pourra aider d'autre presonnes, et si vous aviez une version optimisé, svp, j'attend votre solution:

CREATE OR REPLACE FUNCTION bis_ca_prog_get_adress(
id integer, opostal_address_street character varying,opostal_address_city character varying,opostal_address_state character varying, opostal_address_postcode character varying,
omain_address_street character varying,omain_address_city character varying,omain_address_state character varying, omain_address_postcode character varying,
ipostal_address_street character varying,ipostal_address_city character varying,ipostal_address_state character varying, ipostal_address_postcode character varying,
imain_address_street character varying,imain_address_city character varying,imain_address_state character varying, imain_address_postcode character varying)
RETURNS SETOF address_ind_org_type AS
$BODY$
DECLARE
mon_record address_ind_org_type;
id ALIAS FOR $1;
request_orga int;
request_cont int;
BEGIN
request_orga := contact_organisation_stk_stakeholder_id from ca_programme where ca_programme_id = id;
request_cont := contact_stk_stakeholder_id from ca_programme where ca_programme_id = id;

  if request_orga is not null then
        if (opostal_address_street !='' or opostal_address_city !='' or opostal_address_state !='' or opostal_address_postcode !='') then
            FOR mon_record IN
        SELECT case when so2.postal_address_street != '' then so2.postal_address_street else '[INSERT THE STREET AND NUMBER]' end as address_street,
               case when so2.postal_address_city != '' then so2.postal_address_city else '[INSERT THE CITY/SUBURB]' end as address_city,
               case when so2.postal_address_state != '' then so2.postal_address_state else '[INSERT THE STATE]' end as address_state,
               case when so2.postal_address_postcode != '' then so2.postal_address_postcode else '[INSERT THE POSTAL CODE]' end as address_postcode
        FROM ca_programme p
        LEFT JOIN catalog_table fs ON p.ca_cat_fund = fs.catalog_table_id
        LEFT JOIN stk_stakeholder ss2 ON ss2.stk_stakeholder_id = p.contact_stk_stakeholder_id
        LEFT JOIN ind_individual i2 ON ss2.individual_id = i2.individual_id
        LEFT JOIN vw_stk_stakeholder vs2 ON vs2.stk_stakeholder_id = p.contact_organisation_stk_stakeholder_id
        LEFT JOIN stk_organisation so2 ON vs2.stk_organisation_id = so2.stk_organisation_id
        left join (
          SELECT * FROM cat_currency WHERE currency_country = (SELECT param_value FROM cat_global_params WHERE param_name = 'Country')
        ) bar on true
        WHERE ca_programme_id = id   
                loop
             return next mon_record;
                end loop;
       
             elsif (omain_address_street !='' or omain_address_city !='' or omain_address_state !='' or omain_address_postcode !='') then
            FOR mon_record IN
        SELECT case when so2.main_address_street != '' then so2.main_address_street else '[INSERT THE STREET AND NUMBER]' end as address_street,
               case when so2.main_address_city != '' then so2.main_address_city else '[INSERT THE CITY/SUBURB]' end as address_city,
               case when so2.main_address_state != '' then so2.main_address_state else '[INSERT THE STATE]' end as address_state,
               case when so2.main_address_postcode != '' then so2.main_address_postcode else '[INSERT THE POSTAL CODE]' end as address_postcode
        FROM ca_programme p
        LEFT JOIN catalog_table fs ON p.ca_cat_fund = fs.catalog_table_id
        LEFT JOIN stk_stakeholder ss2 ON ss2.stk_stakeholder_id = p.contact_stk_stakeholder_id
        LEFT JOIN ind_individual i2 ON ss2.individual_id = i2.individual_id
        LEFT JOIN vw_stk_stakeholder vs2 ON vs2.stk_stakeholder_id = p.contact_organisation_stk_stakeholder_id
        LEFT JOIN stk_organisation so2 ON vs2.stk_organisation_id = so2.stk_organisation_id
        left join (
          SELECT * FROM cat_currency WHERE currency_country = (SELECT param_value FROM cat_global_params WHERE param_name = 'Country')
        ) bar on true
        WHERE ca_programme_id = id   
                loop
             return next mon_record;
                end loop;
         end if;
     

elsif (request_orga is null and request_cont is not null) then
        if (ipostal_address_street !='' or ipostal_address_city !='' or ipostal_address_state !='' or ipostal_address_postcode !='') then
            FOR mon_record IN
        SELECT case when i2.postal_address_street != '' then i2.postal_address_street else '[INSERT THE STREET AND NUMBER]' end as address_street,
               case when i2.postal_address_city != '' then i2.postal_address_city else '[INSERT THE CITY/SUBURB]' end as address_city,
               case when i2.postal_address_state != '' then i2.postal_address_state else '[INSERT THE STATE]' end as address_state,
               case when i2.postal_address_postcode != '' then i2.postal_address_postcode else '[INSERT THE POSTAL CODE]' end as address_postcode
        FROM ca_programme p
        LEFT JOIN catalog_table fs ON p.ca_cat_fund = fs.catalog_table_id
        LEFT JOIN stk_stakeholder ss2 ON ss2.stk_stakeholder_id = p.contact_stk_stakeholder_id
        LEFT JOIN ind_individual i2 ON ss2.individual_id = i2.individual_id
        LEFT JOIN vw_stk_stakeholder vs2 ON vs2.stk_stakeholder_id = p.contact_organisation_stk_stakeholder_id
        LEFT JOIN stk_organisation so2 ON vs2.stk_organisation_id = so2.stk_organisation_id
        left join (
          SELECT * FROM cat_currency WHERE currency_country = (SELECT param_value FROM cat_global_params WHERE param_name = 'Country')
        ) bar on true
        WHERE ca_programme_id = id   
                loop
             return next mon_record;
                end loop;
       
             elsif (imain_address_street !='' or imain_address_city !='' or imain_address_state !='' or imain_address_postcode !='') then
            FOR mon_record IN
        SELECT case when i2.main_address_street != '' then i2.main_address_street else '[INSERT THE STREET AND NUMBER]' end as address_street,
               case when i2.main_address_city != '' then i2.main_address_city else '[INSERT THE CITY/SUBURB]' end as address_city,
               case when i2.main_address_state != '' then i2.main_address_state else '[INSERT THE STATE]' end as address_state,
               case when i2.main_address_postcode != '' then i2.main_address_postcode else '[INSERT THE POSTAL CODE]' end as address_postcode
        FROM ca_programme p
        LEFT JOIN catalog_table fs ON p.ca_cat_fund = fs.catalog_table_id
        LEFT JOIN stk_stakeholder ss2 ON ss2.stk_stakeholder_id = p.contact_stk_stakeholder_id
        LEFT JOIN ind_individual i2 ON ss2.individual_id = i2.individual_id
        LEFT JOIN vw_stk_stakeholder vs2 ON vs2.stk_stakeholder_id = p.contact_organisation_stk_stakeholder_id
        LEFT JOIN stk_organisation so2 ON vs2.stk_organisation_id = so2.stk_organisation_id
        left join (
          SELECT * FROM cat_currency WHERE currency_country = (SELECT param_value FROM cat_global_params WHERE param_name = 'Country')
        ) bar on true
        WHERE ca_programme_id = id   
                loop
             return next mon_record;
                end loop;
        end if;    
       
else --if request_cont is null then
    FOR mon_record IN
        SELECT '[INSERT THE STREET AND NUMBER]' as address_street, '[INSERT THE CITY/SUBURB]' as address_city,'[INSERT THE STATE]' as address_state,
        '[INSERT THE POSTAL CODE]' as address_postcode
    loop
        return next mon_record;
        end loop;

end if;
       
return;
END
$BODY$
LANGUAGE plpgsql STABLE;
ALTER FUNCTION bis_ca_prog_get_adress(
id integer, postal_address_street character varying,postal_address_city character varying,postal_address_state character varying, postal_address_postcode character varying,
main_address_street character varying,main_address_city character varying,main_address_state character varying, main_address_postcode character varying)
  OWNER TO postgres;

---

www.marchegouro.com