2

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:

  • Ignore errors. I want psql to exit with an error if the script contains i.e. sql syntax errors.
  • Use plpgsql functions instead of sql. Of course I could, but simple sql functions are often the best choice.
  • Create the table first. My real scenario is actually more complex than this example.
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Tom-db
  • 6,528
  • 3
  • 30
  • 44
  • Do you know the names of the affected tables (and the corresponding functions) in advance ( := before they even exist) ? – joop Apr 14 '15 at 12:02
  • You could use a table as *application catalog* where the tables (and functions) are registered. With triggers on this table you could automate the generation of the actual functions (replacing the stub-functions) and maybe even the tables. – joop Apr 16 '15 at 09:04

3 Answers3

11

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

Martin Bouladour
  • 339
  • 2
  • 8
4

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I have actually a big number of scripts and functions and I don't want to check the database schema in every function. I hoped there is a simple option in psql (or inside the sql function?) which tells to postgres 'do not check the schema!' – Tom-db Mar 20 '15 at 09:11
  • @TommasoDiBucchianico: I wouldn't know of any option to suppress parsing during function creation. I added quote and link to the manual. – Erwin Brandstetter Mar 20 '15 at 16:37
  • Thank you Erwin, I will use pl/pgsql instead of sql procedures. – Tom-db Apr 16 '15 at 09:31
0

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 ....
frlan
  • 6,950
  • 3
  • 31
  • 72
  • Thanks but I cannot create the table first. My real scenario is actually more compex than this example. – Tom-db Mar 20 '15 at 08:40