postgresql - 无法单独获取所有列值，而是获取一列中的所有值 postgresql --9.5

Question

CREATE OR REPLACE FUNCTION public.get_locations(
    location_word varchar(50)
    ) 
RETURNS TABLE
(
    country varchar(50),
    city varchar(50)
)
AS $$
DECLARE
location_word_ varchar(50);
BEGIN
location_word_:=concat(location_word, '%');
RETURN QUERY EXECUTE format(' (SELECT c.country, ''''::varchar(50) as city FROM webuser.country c 
                                    WHERE lower(c.country)  LIKE %L  LIMIT 1)
                                UNION
                                (SELECT c.country,ci.city FROM webuser.country c
                                    JOIN webuser.city ci ON c.country_id=ci.country_id
                                    WHERE lower(ci.city) LIKE %L LIMIT 4)', 
                                    location_word_,
                                    location_word_ )    ;

END
$$ language PLPGSQL STABLE;

SELECT public.get_locations('a'::varchar(50)); 

我明白了；

+get_locations   +
+record          +
------------------
+(Andorra,"")    + 
+(Germany,Aach)  +
+(Germany,Aalen) +
+(Germany,Achim) +
+(Germany,Adenau)+

如何像下面这样逐列放置/获取值？因为否则我无法正确匹配这些值。我应该逐列获取国家和城市等值。

|country         | city       |
-------------------------------
| Andorra        | ""         |
| Germany        | Aach       |
| Germany        | Aalen      |
| Germany        | Achim      |
| Germany        | Adenau     | 

Answer 1

您的函数被声明为返回表，因此您必须像使用表一样使用它:

SELECT *
FROM public.get_locations('a'::varchar(50)); 

---

不相关，但是:

您的函数太复杂了，您不需要动态 SQL，也不需要 PL/pgSQL 函数。

您可以将其简化为:

CREATE OR REPLACE FUNCTION public.get_locations(p_location_word varchar(50)) 
   RETURNS TABLE(country varchar(50), city varchar(50))
AS $$
(SELECT c.country, ''::varchar(50) as city 
 FROM webuser.country c 
 WHERE lower(c.country) LIKE concat(p_location_word, '%')
 LIMIT 1)
UNION ALL
(SELECT c.country ,ci.city 
 FROM webuser.country c
   JOIN webuser.city ci ON c.country_id = ci.country_id
 WHERE lower(ci.city) LIKE concat(p_location_word, '%')
 LIMIT 4)
$$ 
language SQL;