Postgresql:错误:列 "mayvar"不存在而 myvar 是一个变量

Question

我正在尝试在 plsql 中创建一个函数，以在作为参数传递给该函数的用户的 1 英里范围内查找 5 个用户。

这是我的代码:

    CREATE OR REPLACE FUNCTION testfunction (integer) RETURNS integer as $$
    DECLARE
    myvar integer := $1;
    mylon double precision; 
    mylat double precision; 
    lon1 float; 
    lon2 float; 
    lat1 float; 
    lat2 float;
    BEGIN
    raise notice 'help = %', $1; --just for testing
    raise notice 'myvar = %', myvar; --again for testing
    select cr.last_known_longitude, cr.last_known_latitude into mylon, mylat from current_reg as cr where userid = myvar;
    lon1 = mylon - 1 / abs(cos(radians(mylat))*69);
    lon2 = mylon + 1/abs(cos(radians(mylat))*69);
    lat1 = mylat - (1/69);
    lat2 = mylat + (1/69);
    create view myview as
            select destination.userid, 3956*2*asin(sqrt(power(sin((origin.last_known_latitude-destination.last_known_latitude)*pi()/180 / 2), 2) + cos(origin.last_known_latitude* pi()/180) * cos(destination.last_known_latitude* pi()/180) * power(sin((origin.last_known_longitude-destination.last_known_longitude) * pi()/180 /2), 2))) as distance
            from current_reg destination, current_reg origin
            where origin.userid = myvar
            and destination.last_known_longitude between lon1 and lon2
            and destination.last_known_latitude between lat1 and lat2
            having distance < 1 order by distance limit 5;
    return 0;
    END; 
    $$ LANGUAGE 'plpgsql';
    Select testfunction(7);

其中 current_reg 是一个以 userid、last_known_latitude、last_known_longitude 作为列的表。作为整数传递给函数的参数是用户的用户 ID，我希望从其位置(纬度和经度)找到一英里范围内的用户。

我收到以下错误:

NOTICE:  help = 7
NOTICE:  myvar = 7

ERROR:  column "myvar" does not exist
LINE 1: ...ination, current_reg origin where origin.userid = myvar and de...
                                                             ^
QUERY:  create view myview as select destination.userid, 3956*2*asin(sqrt(power(sin((origin.last_known_latitude-destination.last_known_latitude)*pi()/180 / 2), 2) + cos(origin.last_known_latitude* pi()/180) * cos(destination.last_known_latitude* pi()/180) * power(sin((origin.last_known_longitude-destination.last_known_longitude) * pi()/180 /2), 2))) as distance from current_reg destination, current_reg origin where origin.userid = myvar and destination.last_known_longitude between lon1 and lon2 and destination.last_known_latitude between lat1 and lat2 having distance < 1 order by distance limit 5
CONTEXT:  PL/pgSQL function testfunction(integer) line 19 at SQL statement
********** Error **********

ERROR: column "myvar" does not exist
SQL state: 42703
Context: PL/pgSQL function testfunction(integer) line 19 at SQL statement

既然“myvar”是一个变量，为什么它期望它是一个列？

在@a_horse_with_no_name 的帮助下，这是修改后的代码

    CREATE OR REPLACE FUNCTION testfunction (p_userid integer) 
    RETURNS table (userid integer, distance float) 
    AS 
    $$
    DECLARE
    mylon double precision; 
    mylat double precision; 
    lon1 float; 
    lon2 float; 
    lat1 float; 
    lat2 float;
    BEGIN
    raise notice 'help = %', p_userid; --just for testing

    select cr.last_known_longitude, cr.last_known_latitude 
    into mylon, mylat 
    from current_reg as cr 
    where cr.userid = p_userid;

    lon1 = mylon - 1 / abs(cos(radians(mylat))*69);
    lon2 = mylon + 1/abs(cos(radians(mylat))*69);
    lat1 = mylat - (1/69);
    lat2 = mylat + (1/69);

    return query 
       select destination.userid, 
       3956*2*asin(sqrt(power(sin((origin.last_known_latitude-destination.last_known_latitude)*pi()/180 / 2), 2) + cos(origin.last_known_latitude* pi()/180) * cos(destination.last_known_latitude* pi()/180) * power(sin((origin.last_known_longitude-destination.last_known_longitude) * pi()/180 /2), 2)))
       as distance 
       from current_reg as destination JOIN current_reg as origin 
       where origin.userid = p_userid
       and destination.last_known_longitude between lon1 and lon2 
       and destination.last_known_latitude between lat1 and lat2 
       having distance < 1 
       order by distance limit 5;

    END; 
    $$ LANGUAGE plpgsql;
    Select userid from testfunction(4);

我现在收到以下错误:错误:“where”处或附近的语法错误第 29 行:其中 origin.userid = p_userid ^

Answer 1

如果要返回查询的结果，需要在PL/pgSQL中使用return query。您不能像这样在 DDL 语句中使用变量。为函数的每次 调用创建一个 View 是一个非常糟糕的主意。更重要的是:您的函数在第二次调用时会失败，因为 View 已经存在。

根据您所写的内容，我认为您想要这样的东西:

CREATE OR REPLACE FUNCTION testfunction (p_userid integer) 
  RETURNS table (userid integer, distance float) 
AS 
$$
DECLARE
  mylon double precision; 
  mylat double precision; 
  lon1 float; 
  lon2 float; 
  lat1 float; 
  lat2 float;
BEGIN
  raise notice 'help = %', p_userid; --just for testing

  select cr.last_known_longitude, cr.last_known_latitude 
      into mylon, mylat 
  from current_reg as cr 
  where userid = p_userid;

  lon1 = mylon - 1 / abs(cos(radians(mylat))*69);
  lon2 = mylon + 1/abs(cos(radians(mylat))*69);
  lat1 = mylat - (1/69);
  lat2 = mylat + (1/69);

  return query 
    select destination.userid, 
           3956*2*asin(sqrt(power(sin((origin.last_known_latitude-destination.last_known_latitude)*pi()/180 / 2), 2) + cos(origin.last_known_latitude* pi()/180) * cos(destination.last_known_latitude* pi()/180) * power(sin((origin.last_known_longitude-destination.last_known_longitude) * pi()/180 /2), 2))) as distance 
    from current_reg destination, 
         current_reg origin 
    where origin.userid = p_userid
      and destination.last_known_longitude between lon1 and lon2 
      and destination.last_known_latitude between lat1 and lat2 
    having distance < 1 
    order by distance limit 5;

END; 
$$ LANGUAGE plpgsql;

1. 你想返回查询结果，所以你的函数应该声明为returns table。
2. 您可以为参数命名，无需通过复制来混淆您的代码。
3. 要在 PL/pgSQL 中返回查询结果，您需要return query。

要获取该函数的结果，请使用:

select *
from testfunction(1); 

注意函数在 FROM 子句中，而不是在 SELECT 列表中!

仍然看起来可疑的事情:

1. 您没有连接两个表 from current_reg destination, current_reg origin 在两者之间创建一个交叉连接。为什么在 where 子句中使用显式 JOIN 比旧的隐式连接更好的另一个很好的例子
2. 您正在使用有但没有使用任何聚合