PostgreSQL create function that references a non existing table

Question

I'm trying to define a function that references a temp table that will be defined in the future, but I'm getting the following error on creation:

DROP FUNCTION IF EXISTS test_function();
CREATE FUNCTION test_function() 
RETURNS void AS $$
    SELECT * FROM temp_table
$$ LANGUAGE SQL;

ERROR: relation "_temp_table" does not exist LINE 7: FROM temp_table

Is there a way to define a Postgresql function that referenes a table that doesn't exist?

I can create an empty temp table as part of the function creation - but it's a bit ugly...

Any help will be appreciated...

Thanks

You should to use plpgsql language. When I see your example, please, start with read documentation postgresql.org/docs/current/static/plpgsql.html — Pavel Stehule
– Pavel Stehule, Commented Aug 24, 2018 at 13:40
Plus you can't say it returns void but then make it return everything from your table. — 404
– 404, Commented Aug 24, 2018 at 13:46
I think your comments are slightly off topic here... I was making a point with my example regarding the issue I had with a nonexisting table - there's no point in this function anyway...:) — Pavel Durov
– Pavel Durov, Commented Aug 29, 2018 at 13:50

JGH · Accepted Answer · 2018-08-24 17:27:00Z

2

You can use check_function_bodies to turn off the references while loading the functions, as it is done by pg_dump.

set check_function_bodies = off;

DROP FUNCTION IF EXISTS test_function();
CREATE FUNCTION test_function() 
RETURNS void AS $$
    //your code actually doing something with tables/functions that don't exist yet
$$ LANGUAGE SQL;

set check_function_bodies = on;

answered Aug 24, 2018 at 17:27

JGH

18.2k5 gold badges40 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pavel Durov Over a year ago

Thank you, really helpfull!

Collectives™ on Stack Overflow

PostgreSQL create function that references a non existing table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related