CODEWITHSUNDEEP
postgresqlfunctionplpgsql
Jonas Olsen
Jonas Olsen

Reputation: 23

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?

Upvotes: 2

Views: 267

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656411

Creating and accessing a table in the same SQL function is generally impossible. Makes no difference whether you create the table in the SQL function directly or in a nested function call. All objects must be visible to begin with.

There is a big, fat note at the top of the chapter Query Language (SQL) Functions in the manual pointing that out:

Note

The entire body of a SQL function is parsed before any of it is executed. While a SQL function can contain commands that alter the system catalogs (e.g., CREATE TABLE), the effects of such commands will not be visible during parse analysis of later commands in the function. Thus, for example, CREATE TABLE foo (...); INSERT INTO foo VALUES(...); will not work as desired if packaged up into a single SQL function, since foo won't exist yet when the INSERT command is parsed. It's recommended to use PL/pgSQL instead of a SQL function in this type of situation.

Consider a PL/pgSQL function instead. Or maybe there is a (more efficient) way without temp table.

Related:

Upvotes: 3

Pavel Stehule
Pavel Stehule

Reputation: 45770

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).

Upvotes: 1

Related Questions