1

I'm sure someone has asked this question before but I wasn't able to find it by searching around. So at least if it gets marked as a duplicate, maybe it'll be easier to find here. The Postgres docs have not helped me.

I have the following SQL:

create table foobar(col1 bool);
create or replace function foo(tablename varchar) returns void as $func$
begin
  alter table tablename add column if not exists bar boolean;
end;
$func$ language plpgsql;
select foo('foobar');

What I expect to happen:

  • Create a table foobar with boolean column col1
  • Create a function foo which adds a boolean column bar to specified table
  • Run function to add this column to table foobar

What actually happens, is that the first two SQL statements are executed successfully but the third fails:

ERROR: relation "tablename" does not exist

My IDE is telling me that this should be fine. I have fussed with this for a while and have not been able to come up with a way to make this cooperate except by using format() string replacement:

create or replace function foo(tablename varchar) returns void as $func$
begin
  execute(format('alter table %I add column if not exists bar boolean;', tablename));
end;
$func$ language plpgsql;

Is there really no other way of doing this?

3
  • 1
    Variables cannot represent identifiers in a SQL query -- unless you use dynamic SQL. Your method is fine. Commented Feb 6, 2018 at 18:23
  • the only approach to run it without execute format would be if tablename = 'blah' then alter table blah add column blah_blah which obviously is much worse approach then dynamic execute here... So - there is some other way of doing this, but doing it as you do with execute format is best option Commented Feb 6, 2018 at 19:06
  • You presented your question well. But you were right all along on two accounts: 1. You already have the best solution. 2. It has been asked before. Commented Feb 6, 2018 at 23:51

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.