CODEWITHSUNDEEP
postgresqlplpgsqlpsqlsql-function
Tom-db
Tom-db

Reputation: 6868

Create SQL function referring to a table or column that does not exist (yet)

I want to load some SQL functions in a empty database through psql:

psql -d my_database -f fuctions.sql --set ON_ERROR_STOP=1

I use --set ON_ERROR_STOP=1 because I want that psql fails if the script contains errors.

The content of functions.sql is:

CREATE or REPLACE FUNCTION test_function() RETURNS INT AS $$
  SELECT id from test_table;
$$ LANGUAGE sql;

My problem is, that psql checks if test_table exists when loading function and fails with this error:

ERROR:  relation "test_table" does not exist LINE 2: SELECT id from test_table;

But I don't want that psql check if the table exists because I will create this table later.

Following workarounds would work but I cannot use them:

Upvotes: 3

Views: 3303

Answers (3)

Martin Bouladour
Martin Bouladour

Reputation: 359

You can set the configuration variable check_function_bodies to false before creating the functions.

For instance, this should let you create your test function even though test_table doesn't exist:

BEGIN;
SET LOCAL check_function_bodies TO FALSE;
CREATE or REPLACE FUNCTION test_function() RETURNS INT AS $$
  SELECT id from test_table;
$$ LANGUAGE sql;
COMMIT;

Documentation: http://www.postgresql.org/docs/9.5/static/runtime-config-client.html#GUC-CHECK-FUNCTION-BODIES

Upvotes: 13

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656421

The error message comes from Postgres, not from psql.

Workaround

If you cannot create the table first (for what ever reason), you could "fake it until you make it": Create a temporary table with matching structure. You only need column names and types to match. For your example function:

CREATE TEMP TABLE test_table (id int);

Then CREATE FUNCTION goes through. Dropping the table later is not prohibited. Postgres does not save dependencies for code in the function body. So you can drop the table once the function is created. If you call the function after dropping the temp table, you get an error.

After you create the actual table later, the function will work normally.

Disable parsing of SQL function at creation time?

To my knowledge, this is not possible. Maybe there is a compile-time option for Postgres to disable it. The manual advises to use PL/PgSQL for cases like yours:

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.

Bold emphasis mine.

Upvotes: 4

frlan
frlan

Reputation: 7260

You could split up yur sql-file e.g. into DDL, DML and then executing the functions.

So something like

file1:

CREATE TABLE foo (
   id int primary key,
   data int);

file2:

CREATE or REPLACE FUNCTION test_function() RETURNS INT AS $$
  SELECT id from test_table;
$$ LANGUAGE sql;
....

And then calling psql something like 

psql -f file1 
psql -f file2
psql -f ....

Upvotes: 0

Related Questions