How to create a table inside function that not view from outside?

Question

I have below function in postgres. This works fine, but if this called concurrently by two users it throw an exception:

Exception : ERROR: relation "callInfo" already exists.

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;

How do I handle this?

Why are you not using a CTE: `WITH callinfo AS (SELECT...)` or a simple subquery? You could save your result with `SELECT INTO`... I don't see any need for creating a separate table. — S-Man, Sep 25 '18 at 14:41
can say a example, callinfo is a table , can store table by 'SELECT INTO'? if yes please tell me how. — Ali Bagheri, Sep 25 '18 at 14:46
Maybe this helps https://stackoverflow.com/questions/32913214/declare-row-type-variable-in-pl-pgsql — S-Man, Sep 25 '18 at 14:56
I withdraw my statement concerning no need of TABLE. Using a temporary table as @LaurenzAlbe mentioned is a really good idea. Especially (I just leared it right now) SELECT INTO is similar to CREATE TEMP TABLE https://stackoverflow.com/a/11979191/3984221 — S-Man, Sep 25 '18 at 15:44

score 1 · Answer 1 · answered Sep 25 '18 at 15:08

The solution is to use a temporary table.

A temporary table is created with

CREATE TEMPORARY TABLE ...

and is only visible in the database session that created it.

Depending on how you create it, it is automatically dropped when the transaction or the database session ends, but that is not relevant here, since you drop the table explicitly.

score 1 · Answer 2 · answered Sep 25 '18 at 15:46

demo: db<>fiddle

Using a TEMP TABLE would do it (see answer of @LaurenzAlbe for that)

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

score 0 · Answer 3 · answered Sep 25 '18 at 16:31

Yes, I too used temp, but i think all user connecting request to DB have same session, now understand any request has separate session.

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; $$;

How to create a table inside function that not view from outside?

3 Answers3