sarlav kumar wrote:

> Hi,
>  
> I tried using execute script to add a column to a table. But it 
> doesn't work.
> I read in articles that it is possible to execute a set of alter table 
> commands using execute script.
>  
> Here is what I tried: (I am pasting only the relevant stuff)
>  
>         #--
>         execute script ( SET ID = 1, FILENAME = 'DDL.sql', EVENT NODE 
> = 1);
>         echo 'DDL executed';
>         #--
> DDL.sql file contains:
> alter table newt ADD column newcol int;
>  
> I also have other files DDL1.sql, DDL2.sql which contain the following 
> respectively
> alter table newt alter column newcol set default 0;
>  
> update newt SET newcol = 0 where newcol is null;
>  
> When I execute DDL.sql, I get the following error:
> <stdin>:16: PGRES_FATAL_ERROR select "_test".ddlScript(1, 'alter table 
> newt ADD column newcol int;
> ');  - ERROR:  column "newcol" of relation "newt" already exists
> CONTEXT:  PL/pgSQL function "ddlscript_int" line 45 at execute statement
> PL/pgSQL function "ddlscript" line 30 at perform
> But in the subscriber node, the column does not exist. What am I doing 
> wrong here?
> When I add the column manually in the subscriber node and execute DDL1 
> and DDL2, I dont have any problem.
>  

The problem is that the DDL script needs to be defined suitably such 
that it may be run on ALL the nodes.

(You can select to run it only on one node, which would change the rule 
a bit...)

In version 1.1, I added the following function that can do this sort of 
thing in a more selective manner:

create or replace function @NAMESPACE at .add_missing_table_field (text, 
text, text, text)
returns bool as '
DECLARE
  p_namespace alias for $1;
  p_table     alias for $2;
  p_field     alias for $3;
  p_type      alias for $4;
  v_row       record;
  v_query     text;
BEGIN
  select 1 into v_row from pg_namespace n, pg_class c, pg_attribute a
     where quote_ident(n.nspname) = p_namespace and
         c.relnamespace = n.oid and
         quote_ident(c.relname) = p_table and
         a.attrelid = c.oid and
         quote_ident(a.attname) = p_field;
  if not found then
    raise notice ''Upgrade table %.% - add field %'', p_namespace, 
p_table, p_field;
    v_query := ''alter table '' || p_namespace || ''.'' || p_table || '' 
add column '';
    v_query := v_query || p_field || '' '' || p_type || '';'';
    execute v_query;
    return ''t'';
  else
    return ''f'';
  end if;
END;' language plpgsql;

You'd run this by invoking the DDL script:
  select "_test".add_missing_table_field('public', 'newt', 'newcol', 'int');

On any nodes where the column is already there, it won't do anything.  
On nodes where it is missing, it will get created.

Adapting that to work in a 1.0.5 environment will require hacking that 
function into place...