将表格作为参数传递 [英] Passing the table as a parameter

问题描述

我必须从lat和long转换为geom才能使用PostGIS.我的问题是，我有来自不同位置的各种表，我想将该表作为参数传递给函数.我正在尝试:

I have to convert from lat and long to geom to use PostGIS. My problem, I have various tables from different locations and I want to pass the table as a parameter to the function. I'm trying this:

CREATE or REPLACE FUNCTION convert_from_lon_lat(float,float,character varying)      
RETURNS  integer AS $$
select id from $3 as vertices 
order by  vertices.geom <-> ST_SetSrid(ST_MakePoint($1,$2),4326) LIMIT 1;
$$ LANGUAGE SQL;

但是出现语法错误.

所以我将之前的代码更改为此:

So I changed the previous code to this:

CREATE or REPLACE FUNCTION convert_from_lon_lat(long float, lat float, _table character varying) RETURNS  integer AS $$
BEGIN
EXECUTE('select id from _table as vertices order by  vertices.geom <-> ST_SetSrid(ST_MakePoint(long,lat),4326) LIMIT 1;');
END;
$$ LANGUAGE plpgsql;

它创建起来没有任何问题，但是当我称之为`convert_from_lon_lat(long1，long2，my_table)

it creates without any problem, but when I call it `convert_from_lon_lat(long1, long2, my_table)

我明白了并报错:

ERROR:  relation "_table" does not exist

它没有将表名作为参数传递

It's not passing the table name as an argument

CREATE or REPLACE FUNCTION convert_from_lon_lat(long float, lat float, tbl character varying) RETURNS  integer AS $func$
BEGIN
EXECUTE format('select id from %s order by  %s.the_geom <-> ST_SetSrid(ST_MakePoint('|| long || ','|| lat ||'),4326) LIMIT 1;', tbl, tbl);
END;
$func$ LANGUAGE plpgsql;

现在，当我调用该函数时，我会收到一个错误:控件已到达函数末尾而没有返回值"

Now when I call the function, I get an `ERROR: control reached end of function without RETURN``

我尝试了RETURN QUERY EXECUTE format('...，但得到了ERROR: cannot use RETURN QUERY in a non-SETOF function

推荐答案

如@ @dezso所述，在这种情况下，您将需要动态SQL.

AS @dezso mentioned, you'll need dynamic SQL in this case.

因此，您在正确的轨道上；使用PL/pgSQL形成动态SQL语句，但是您只需要最后一点:

So, you're on the right track; forming a dynamic SQL statement using PL/pgSQL, but you just need the finishing touches:

CREATE or REPLACE FUNCTION convert_from_lon_lat(long float, lat float, _table text) 
RETURNS integer AS $$
BEGIN
RETURN QUERY EXECUTE format('SELECT id FROM %I AS vertices 
  ORDER BY vertices.geom <->ST_SetSrid(ST_MakePoint(long,lat),4326) LIMIT 1;',_table);
END
$$ LANGUAGE plpgsql;

我相信这应该可以解决您的问题.

I believe this should solve your issues.

注意:我们发现上述解决方案存在错误，并且使用SETOF，我尝试纠正以下问题.

Note: We've discovered an error with the above solution and using SETOF, I've attempted to correct the issues below.

这里有一些编辑，希望有一个解决方案可以解决您的问题.另外，请原谅我以前的& ;;当前的解决方案；我现在没有时间测试它们. :(

A few edits here, hopefully one solution will fix your issue. Also, please excuse any syntax errors in my previous & current solutions; I don't have time to test them right now. :(

1)，您可以尝试返回一个SETOF整数，因为您当然会只返回一个整数.在这种情况下，您的返回类型将是一个包含整数的单列单行.

1) You could just try returning a SETOF integers, knowing that of course you'll only return the one. Your return type in this case will then be a single, one-column row containing an integer.

CREATE or REPLACE FUNCTION convert_from_lon_lat(long float, lat float, _table text) 
RETURNS SETOF integer AS $$
BEGIN
RETURN QUERY EXECUTE format('SELECT id FROM %I AS vertices 
  ORDER BY vertices.geom <->ST_SetSrid(ST_MakePoint(long,lat),4326) LIMIT 1;',_table);
END
$$ LANGUAGE plpgsql;

然后调用为:

SELECT * FROM convert_from_lon_lat(...);

2)要专门返回整数，我想您可以尝试以下方法:

2) To specifically return an integer, I think you can try this:

CREATE or REPLACE FUNCTION convert_from_lon_lat(long float, lat float, _table text) 
RETURNS integer AS $$

DECLARE
return_id integer;

BEGIN
EXECUTE format('SELECT id FROM %I AS vertices 
  ORDER BY vertices.geom <->ST_SetSrid(ST_MakePoint(long,lat),4326) LIMIT 1;',_table)
  INTO return_id;

RETURN return_id;
END
$$ LANGUAGE plpgsql;

这篇关于将表格作为参数传递的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！