代码之家 › 专栏 › 技术社区 › Ali Bagheri

如何在函数内部创建一个不从外部查看的表?

local postgresql sql

Ali Bagheri · 技术社区 · 7 年前

我在博士后有以下职能。这个很好,

异常:错误:关系“callInfo”已存在。

CREATE OR REPLACE FUNCTION getObserverItem(wher INT)
RETURNS void
AS $$
declare
  observerRecord RECORD;
  c INT;
BEGIN

  FOR observerRecord IN SELECT * FROM "CustomersVsObserver" WHERE observerid = wher LOOP

    CREATE LOCAL TEMP TABLE callInfo AS SELECT * FROM "CustomerCallingInfo" WHERE customerid = observerRecord.id;
    c := (SELECT *,MAX(callDate) AS lastDate FROM callInfo WHERE callState notnull);
    .
    . -- I use callInfo in multi select query by different condition.
    .

    DROP TABLE IF EXISTS callInfo;
  END LOOP;

END; $$

LANGUAGE plpgsql;

3 回复 | 直到 7 年前

Laurenz Albe 7 年前

解决方案是使用 临时表 .

临时表是用

CREATE TEMPORARY TABLE ...

并且只在创建它的数据库会话中可见。

S-Man 7 年前

demo: db<>fiddle

使用 TEMP TABLE 会这么做的(参见@LaurenzAlbe的回答)

CREATE OR REPLACE FUNCTION my_function()
RETURNS int
AS $$
declare
  c int;
BEGIN
  CREATE TEMP TABLE saved_query AS
      SELECT * FROM test;

  c := (SELECT SUM(id) FROM saved_query);

  RETURN c;
END; $$

LANGUAGE plpgsql;

Further reading

Ali Bagheri 7 年前

是的,我也使用了temp,但我认为所有连接到DB的用户都有相同的会话,

CREATE OR REPLACE FUNCTION getObserverItem(i_limit INT,i_offset INT, i_observerId INTEGER, i_callStateText TEXT, i_maxText TEXT DEFAULT 'callDate')
  RETURNS SETOF observer_holder
  LANGUAGE plpgsql

AS $$
declare
  count INTEGER;
  observerRecord RECORD;
  customerRecord RECORD;
  adviserRecord RECORD;
  lastCallInfoRec RECORD;
  result observer_holder;

BEGIN

  SELECT COUNT(observerid) INTO count FROM "CustomersVsObserver" INNER JOIN "CustomersVsAdviser" AS t2 ON t2.customerid = "CustomersVsObserver".customerid WHERE observerid = i_observerId;

  IF i_offset = 0 THEN
    IF count < i_limit THEN
      i_limit := count;
    ELSE
      i_offset := count - i_limit;
    END IF;
  ELSE
    IF 0 > i_offset THEN
      i_offset := 0;
    END IF;
  END IF;


  FOR observerRecord IN SELECT * FROM "CustomersVsObserver" WHERE observerid = i_observerId LIMIT i_limit OFFSET i_offset LOOP
    SELECT * INTO adviserRecord FROM "CustomersVsAdviser" WHERE customerid = observerRecord.customerid LIMIT 1;

    IF adviserRecord.adviserid IS NULL THEN
      CONTINUE;
    END IF;

    SELECT * INTO customerRecord FROM "CommonUsersModel" WHERE UserID = observerRecord.customerid LIMIT 1;
    CREATE LOCAL TEMP TABLE callInfo AS SELECT * FROM "CustomerCallingInfo" WHERE customerid = observerRecord.customerid AND adviserid = adviserRecord.adviserid;
    EXECUTE 'SELECT DISTINCT ON (AdviserID,CustomerID) *,MAX('|| i_maxText ||') OVER (PARTITION BY AdviserID,CustomerID) AS lastDate FROM callInfo WHERE callState ' || i_callStateText || ';' INTO lastCallInfoRec;
    --EXECUTE query1 INTO lastCallInfoRec;
    result."CustomerName" := customerRecord.name;
    result."CustomerFamily" := customerRecord.family;
    result."CustomerNumber" := customerRecord.uniquekey;
    result."CustomerID" := observerRecord.customerid;
    result."AdviserID" := adviserRecord.adviserid;
    result."AdviserName" := (SELECT concat(name,' ',family) FROM "CommonUsersModel" WHERE userid = adviserRecord.adviserid);
    result."AllCallCount" := (SELECT COUNT(customerid) FROM callInfo);
    result."ObserverCallCount" := (SELECT COUNT(customerid) FROM callInfo WHERE calldate > observerRecord.setdate);
    result."LastCallTime" := lastCallInfoRec.lastDate;
    result."LastCallState" := lastCallInfoRec.callstate;
    result."OffsetRow" := i_offset;

    RETURN next result;
    DROP TABLE IF EXISTS callInfo;
  END LOOP;

END; $$;