CREATE OR REPLACE FUNCTION select_pi(keywords character varying, geo geometry, dis integer)
RETURNS SETOF RECORD AS
$BODY$
declare
tablecity varchar;
begin
tablecity:=py From chcity where ST_Contains(the_geom,ST_SetSRID('point(120.15413 30.27571)',4326));
return query (SELECT * FROM tablecity where ST_Distance_sphere(mapgeo,geo)<=dis and name like '%'||keywords||'%' order by ST_Distance_sphere(mapldgeo,geo) asc limit 10);
end;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE
COST 100
创建的时候报ERROR: syntax error at or near "$1"
LINE 1: (SELECT * FROM $1 where ST_Distance_sphere(mapgeo, $2 )<= ...
^
QUERY: (SELECT * FROM $1 where ST_Distance_sphere(mapgeo, $2 )<= $3 and name like '%'|| $4 ||'%' order by ST_Distance_sphere(mapgeo, $2 ) asc limit 10)
CONTEXT: SQL statement in PL/PgSQL function "select_pi" near line 5
谁能告诉我变量表名是怎么写的?和如何修改上面的函数
RETURNS SETOF RECORD AS
$BODY$
declare
tablecity varchar;
begin
tablecity:=py From chcity where ST_Contains(the_geom,ST_SetSRID('point(120.15413 30.27571)',4326));
return query (SELECT * FROM tablecity where ST_Distance_sphere(mapgeo,geo)<=dis and name like '%'||keywords||'%' order by ST_Distance_sphere(mapldgeo,geo) asc limit 10);
end;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE
COST 100
创建的时候报ERROR: syntax error at or near "$1"
LINE 1: (SELECT * FROM $1 where ST_Distance_sphere(mapgeo, $2 )<= ...
^
QUERY: (SELECT * FROM $1 where ST_Distance_sphere(mapgeo, $2 )<= $3 and name like '%'|| $4 ||'%' order by ST_Distance_sphere(mapgeo, $2 ) asc limit 10)
CONTEXT: SQL statement in PL/PgSQL function "select_pi" near line 5
谁能告诉我变量表名是怎么写的?和如何修改上面的函数
动态执行,在MYSQL、 SQLSERVER中都是如此
DELIMITER $$DROP PROCEDURE IF EXISTS `zz`.`TTFF`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `TTFF`(IN qqFF VARCHAR(10))
BEGIN
set @ff=concat('select * from ',qqff);
PREPARE stmt1 FROM @ff;
EXECUTE stmt1 ;
END$$DELIMITER ;
ttff 'bygz'CREATE PROCEDURE [dbo].[TTFF](@FF VARCHAR(10))
AS
BEGIN
declare @er varchar(20)
set @er='SELECT * FROM '+@ff
execute (@er)
END
GO
执行,查查POSTGRESQL的手册吧。