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

POSTGRESQL- Query has no destination for result data

提问by kimdasuncion12

I'm new to postgres and to programming and I already searched for solution for this but I couldn't quite get it. I'm trying to make a function that will return information about all the customers on that particular country whenever I call the country. This is the error that pops up. I'm really sorry for asking this but I've been stuck here since yesterday.

我是 postgres 和编程的新手，我已经为此寻找了解决方案，但我不太明白。我正在尝试创建一个函数，该函数将在我致电该国家/地区时返回有关该特定国家/地区的所有客户的信息。这是弹出的错误。我真的很抱歉问这个问题，但我从昨天开始就被困在这里。

ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function country(text) line 5 at SQL statement

错误：查询没有结果数据的目的地
提示：如果您想放弃 SELECT 的结果，请改用 PERFORM。
上下文：PL/pgSQL 函数 country(text) line 5 at SQL 语句

Here is the function:

这是函数：

create or replace function country(text) returns text as $$
begin


  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 = '';
  end;
  $$

  language plpgsql;

回答by Patrick

If you are executing a select statement in a PL/pgSQL function, then you should place the result of the query in some variable(s) (= the destination). Then you work with the variable(s) in the function. You should also have a RETURNstatement.

如果您在 PL/pgSQL 函数中执行 select 语句，那么您应该将查询结果放在某个变量（= 目标）中。然后您使用函数中的变量。你也应该有一个RETURN声明。

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 = ;     -- 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;

Do note that the above only works if the query returns a single row. If the query returns multiple rows you can use a loopin the function. Even simpler, you can just return the results from the query like so:

请注意，以上仅在查询返回单行时才有效。如果查询返回多行，您可以在函数中使用循环。更简单的是，您可以像这样从查询中返回结果：

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 = ;
end;
$$ language plpgsql;

But like Evan Carrollsaid, unless you need a PL/pgSQL function to modify the data before returning it, you are better off with a simple view.

但是就像Evan Carroll所说的那样，除非您需要一个 PL/pgSQL 函数在返回数据之前修改数据，否则最好使用简单的视图。

回答by Riya Bansal

Please use below to get result of given 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 = '';
end;
$$

language plpgsql;

回答by Evan Carroll

This is not normal for SQL. Normally, this would be a VIEW.

这对于 SQL 来说是不正常的。通常，这将是一个VIEW.

CREATE VIEW myView AS
    SELECT customer_id, customer.first_name, customer.last_name 
    FROM customer 
    INNER JOIN address USING (address_id)
    INNER JOIN city USING (city_id)
    INNER JOIN country USING (country_id);

Then you do

然后你做

 SELECT * FROM myView WHERE country = ?

All of that said, if you insist on making this a function, and you shouldn't, you should make it a LANAGUAGE SQLand not LANGUAGE plppsql.

综上所述，如果您坚持将其设为函数，而您不应该这样做，则应将其设为 aLANAGUAGE SQL而不是LANGUAGE plppsql。

回答by Jayram Gaonkar

It worked for my colleague when she used OPEN MYCURSbefore the select query and RETURN MYCURSafter the select query.

当我的同事在选择查询之前使用OPEN MYCURS并在选择查询之后使用RETURN MYCURS时，它对我有用。