How to use query created in stored procedure in other procedure

Question

sql procedure creates temporary table which should used in other sql procedure. I tried

CREATE or replace FUNCTION f_createquery()
  RETURNS TABLE ( kuupaev date
   ) AS $f_createquery$

-- actually this is big and time consuming select statement which should evaluated only once:
select current_date as kuupaev
$f_createquery$ LANGUAGE sql STABLE;


CREATE or replace FUNCTION f_usequery()
  RETURNS TABLE ( kuupaev date
  ) AS $f_usequery$

-- big query tehing is used several times in query:
select kuupaev from tehing
union all
select kuupaev+1 from tehing
union all
select kuupaev+2 from tehing
$f_usequery$ LANGUAGE sql STABLE;


with tehing as (
 select * from f_createquery() _
 )

 select * from f_usequery() _

but got error

ERROR:  relation "tehing" does not exist

tehing contains temporary data created by stored procedure and it does not exist in database. How to allow other stored procedure to use it ? How to fix it for Postgres 9.1+ ?

Is there simething like

external table (kuupaev date)

which allows to define external tables? In real applicaton select statement in f_createquery is big and time consuming and should evaluated only once.

Replacing select * from tehing to dynamic sql in f_usequery() probably works but this prevents procedure compiling at run time. Is there better solution or can tehing passed in better way to other stored procedure, e.q. like parameter ?

Or should f_createquery create temporary table with fixed name tehing?

`f_createquery()` creates the `tehing` temporary table? Can you at least show the part of the stored procedure where the temp table is created? — Patrick, Sep 19 '15 at 10:23

Patrick · Accepted Answer · 2015-09-19T11:45:39.083

If f_createquery() produces the temporary table tehing then it should not return anything. Your function should look like:

CREATE FUNCTION f_createquery() RETURNS void AS $f_createquery$
  CREATE TEMPORARY TABLE tehing AS
    SELECT current_date AS kuupaev;  -- complex query here
$f_createquery$ LANGUAGE sql STABLE;

You can then use the tehing table in the current session like any other table:

CREATE FUNCTION f_usequery() RETURNS TABLE (kuupaev date) AS $f_usequery$
  SELECT kuupaev FROM tehing
  UNION ALL
  SELECT kuupaev+1 FROM tehing
  UNION ALL
  SELECT kuupaev+2 FROM tehing;
$f_usequery$ LANGUAGE sql STABLE;

Note that the temporary table is dropped at the end of the session.

You can also integrate the 2 functions so you can call f_usequery() without worrying about calling another function first:

CREATE FUNCTION f_usequery() RETURNS TABLE (kuupaev date) AS $f_usequery$
BEGIN
  PERFORM 1 FROM information_schema.tables WHERE table_name = 'tehing';
  IF NOT FOUND THEN  -- temp table tehing does not exist
    CREATE TEMPORARY TABLE tehing AS
      SELECT current_date AS kuupaev; -- etc, etc
  END IF;

  RETURN QUERY
    SELECT kuupaev FROM tehing
    UNION ALL
    SELECT kuupaev+1 FROM tehing
    UNION ALL
    SELECT kuupaev+2 FROM tehing;
END; $f_usequery$ LANGUAGE plpgsql STABLE;

Note that this is now a plpgsql function so the syntax is slightly different.

The construct

with tehing as (
 select * from f_createquery() _
)
select * from f_usequery() _

won't work, because you re-declare tehing as the result of the CTE. Instead, f_usequery() works with the tehing temporary table and you can select from it or do further analysis with the result from f_usequery():

SELECT f_createquery(); -- this creates the tehing temporary table

SELECT * FROM f_usequery(); -- this operates on the tehing table and returns some results

SELECT *
FROM tableX
JOIN f_usequery() USING (kuupaev)
WHERE kuupaev < '2015-09-19';

`f_usequery()` creation throws error if tehing table does not exist. How to create f_usequery() once and only use it later? Should dummy tehing table created before f_usequery() is created to avoid creation error ? — Andrus, Sep 19 '15 at 11:11
You could integrate `f_createquery()` into `f_usequery()` by testing for the existence of the temporary table. See updated answer. — Patrick, Sep 19 '15 at 11:41

score 0 · Answer 2 · edited May 23 '17 at 10:27

Simple case: CTE

If you can do everything in a single query, you typically don't need a temp table nor a function at all. A CTE does the job:

WITH tehing AS (
   SELECT current_date AS kuupaev  -- expensive query here
   )
SELECT kuupaev FROM tehing
UNION ALL
SELECT kuupaev+1 FROM tehing
UNION ALL
SELECT kuupaev+2 FROM tehing;

If you need a temp table

A temporary table only makes sense if you actually have to run multiple queries using the result of the expensive query. Or if you need to create an index on the table or something.

Just try to create the temp table:

CREATE TEMP TABLE tehing AS
SELECT current_date AS kuupaev;  -- expensive query here

If the table already exists you get an error, and that's fine as well.
Then run your queries using tehing.

If you really need a function that avoids this error (which I doubt):

CREATE FUNCTION f_create_tbl()
  RETURNS text AS
$func$
BEGIN
   IF to_regclass('pg_temp.tehing') IS NULL THEN  -- temp table does not exist
      CREATE TEMP TABLE tehing AS
      SELECT current_date AS kuupaev;  -- expensive query here

      RETURN 'Temp table "tehing" created.';
   ELSE
      RETURN 'Temp table "tehing" already exists';
   END IF;
END
$func$ LANGUAGE plpgsql;

Call:

SELECT f_create_tbl();

The function cannot be declared STABLE, it's a VOLATILE function (default).

A test like this would be subtly inaccurate:

PERFORM 1 FROM information_schema.tables WHERE table_name = 'tehing';

It would find any table named "tehing" in the search path. But you are only interested if a temporary table of that name exists. The temporary schema is first in the search path by default:

How does the search_path influence identifier resolution and the "current schema"

How to check if a table exists in a given schema

How to use query created in stored procedure in other procedure

2 Answers2

Simple case: CTE

If you need a temp table