如何在PostgreSQL中的函数中传输变量

Question

我正在运行 km_test.sql 来自蝙蝠文件（Windows 7）：

call psql -h ... -U ... -d ... -f C:svntre2progkm_test.sql -v nrl=%a%

其中％a％是整数。我在Postgresql 9.5。

上跑步

km_test.sql 看起来像

set n :nrl
DROP FUNCTION km_test(integer);
CREATE FUNCTION km_test(n integer)
  RETURNS void AS
$BODY$
DECLARE
   j smallint;
BEGIN
   DROP TABLE IF EXISTS km_test CASCADE;
   CREATE UNLOGGED TABLE km_test (
     lnr smallint,
     km_id character varying(16),
     flatenr smallint,
     geo geometry(Linestring,25833)
    );
  j = 1;
  WHILE j < n+1 LOOP
    RAISE NOTICE 'Verdi j er : %', j;
    INSERT INTO km_test (lnr, km_id, flatenr, geo)
      SELECT d.i,
             p.km_id,
             CAST(substring(p.flatenr from 5 for 4) AS smallint),
             ST_MakeLine(p.geo,(ST_Translate(p.geo, d.dx, d.dy)))
      FROM org_tre2.km_punkter_des2016 AS p, org_tre2.km_dxdy1 AS d
      WHERE j = d.i;
    j = j + 1;
  END LOOP;
  COMMENT ON TABLE org_tre2.km_test IS 'KM innsyn: n innsynslinjer for kulturminnepunkt utenfor IK i tre2-flater';
END;
$BODY$
LANGUAGE plpgsql;
set tab 'org_tre2.km_punkter_des2016'
select km_test(:n);

问题：如何使表P（org_tre2.km_punkter_des2016）成为输入参数？意思是如何在函数call中包含taberamam（使用SELECT KM_TULL（：N，：P）），并在函数中参考此表？

CREATE FUNCTION km_test(n integer, t text)
...
select km_test(:n,:'tab')

到目前为止，我尚未设法将输入表称为"从态度"中的t变量。是否可以？还是有解决方法？

Answer 1

感谢您的提示/链接@mcnets。我想我已经弄清楚了。正确的代码 km_test.sql ：

set n :nrl
DROP FUNCTION km_test(integer, text);
CREATE FUNCTION km_test(n integer, t text)
  RETURNS void AS
$BODY$
DECLARE
   j smallint;
   s integer;
BEGIN
   DROP TABLE IF EXISTS km_test CASCADE;
   CREATE UNLOGGED TABLE km_test (
     lnr smallint,
     km_id character varying(16),
     flatenr smallint,
     geo geometry(Linestring,25833)
    );
  j = 1;
  WHILE j < n+1 LOOP
    s:=j;
    RAISE NOTICE 'Verdi j er : %', j;
    RAISE NOTICE 'Tabell t er : %', t;
    EXECUTE 'INSERT INTO km_test (lnr, km_id, flatenr, geo)
      SELECT d.i,
             p.km_id,
             CAST(substring(p.flatenr from 5 for 4) AS smallint),
             ST_MakeLine(p.geo,(ST_Translate(p.geo, d.dx, d.dy)))
      FROM '||t||' as p, org_tre2.km_dxdy1 AS d
      WHERE '||s||' = d.i';
    j = j + 1;
  END LOOP;
  COMMENT ON TABLE org_tre2.km_test IS 'KM innsyn: n innsynslinjer for kulturminnepunkt utenfor IK i tre2-flater';
END;
$BODY$
LANGUAGE plpgsql;
set ptab 'org_tre2.km_punkter_des2016'
select km_test(:n,:'ptab');

使用执行的解决方案，将所有插入的代码放在''中，将s声明为一个变量，然后在were-clause中使用j而不是j。