Search code examples
postgresqlfunctionplpgsql

Can I create and access a table in the same SQL function?

I am trying to create a Postgres SQL-function which runs some routine for my database.

The SQL-function calls a plpgsql-function which creates several temporary tables, but doesn't return anything (RETURNS void). One of the tables created by the plpgsql-function is supposed to be used in my sql-function.

CREATE OR REPLACE FUNCTION public.my_sql_function()
  RETURNS text AS
$BODY$

select public.my_plpsql_function(); -- this returns void, but has created a temp table "tmp_tbl"

DROP TABLE IF EXISTS mytable CASCADE;
CREATE TABLE mytable (
  skov_id int8 PRIMARY KEY,
  skov_stor int4,
  skov_areal_ha numeric,
  virkningfra timestamp(0) without time zone,
  plannoejagtighed float8,
  vertikalnoejagtighed float8,
  geom geometry(MultiPolygon,25832),
  orig_geom geometry(Polygon, 25832) 
);

INSERT INTO mytable
select * from tmp_tbl ....

$BODY$  LANGUAGE sql;

When I try to run the lines, I get the following error:

ERROR: relation "tmp_tbl" does not exist

pgAdmin underlines the line select * from tmp_tbl ... as the part with an error.

So the SQL-function doesn't notice that the plpsql-function has created a temporary table.

Is there a workaround?

Solution

  • I think so it is not possible - and minimally it should not by possible in future versions. SQL functions are similar to views, and then references to database object should be valid in function's creating time.

    There is not any workaround - if you need temp table, use PLpgSQL, or try to write your code without temp table (it can be much better).