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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Peter
  • 1,032
  • 1
  • 11
  • 26
  • 1
    Variables cannot represent identifiers in a SQL query -- unless you use dynamic SQL. Your method is fine. – Gordon Linoff Feb 06 '18 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 – Vao Tsun Feb 06 '18 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. – Erwin Brandstetter Feb 06 '18 at 23:51

0 Answers0