问POSTGRESQL-查询没有结果数据的目的地。

EN

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;

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;

CREATE OR REPLACE FUNCTIOn func_process_client_contact(p_client_version_id bigint, p_publish_id bigint)
    RETURNS TABLE(status_message character varying)
    LANGUAGE plpgsql
AS $function$
declare 
    p_rowcount int;
    BEGIN
        -- validating input parameters : start
        IF ( p_client_version_id IS NULL OR p_publish_id IS NULL )
        THEN 
            RETURN;
        END IF;
        -- validating input parameters : end 

       WITH cte_target AS
       (
            SELECT  
            g.name AS clientname,
            gcr.group_id AS clientid,
            cr.id AS clientrelationid,
            crc.id AS clientrelationcontactid,
            crd.id AS clientrelationdesignation_id
            FROM GROUPS g
            JOIN group_client_relation gcr ON gcr.group_id =g.id
            JOIN client_relation cr ON cr.id = gcr.client_relation_id 
            JOIN client_relation_contact crc ON crc.client_relation_id =cr.id 
            JOIN client_relation_designation crd ON cr.client_relation_designation_id =crd.id
        )
        SELECT * FROM cte_target ct WHERE ct.clientname='ABC';
        GET DIAGNOSTICS p_rowcount = ROW_COUNT;
        RAISE NOTICE 'returned % rows', p_rowcount;
        IF ( p_rowcount=0 )
        THEN 
           RETURN query SELECT 'hello' AS status_message;
        ELSE
            RETURN query SELECT 'Success' AS status_message;
        END IF; 
    END
$function$

create or replace function country(in_parameter text,out out_res refcursor) as $$
begin
open out_res for
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;