On November 18, 2004 07:48 am, Christopher Browne wrote:

>I have the following as somewhat "version-unaware" upgrade scheme
>which can cope with the case of needing to add a column to a table:
>
> create or replace function @NAMESPACE at .upgrade_sl_node () returns bool
> as '
> DECLARE
> ? ? v_row ?record;
> BEGIN
> ? ?if add_missing_table_field(@NAMESPACE@, ''sl_node'', ''no_spool'',
> ''boolean'') then alter table @NAMESPACE at .sl_node
> ? ? ? ? ? ? alter column no_spool set default = ''f'';
> ? ? ? ? update @NAMESPACE at .sl_node set no_spool = ''f'';
> ? ? ? ? return ''t'';
> ? ?end if;
> ? ?return ''t'';
> END;' language plpgsql;
>
> comment on function @NAMESPACE at .upgrade_sl_node() is
> ? 'Schema changes required to upgrade to version 1.1';
>
> 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 n.nspname = p_namespace and
> ? ? ? ? ?c.relname = n.oid and
> ? ? ? ? ?c.relname = p_table and
> ? ? ? ? ?a.attrelid = c.oid and
> ? ? ? ? ?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 || '' type '' || p_type
> || '';''; execute v_query;
> ? ? return ''t'';
> ? else
> ? ? return ''f'';
> ? end if;
> END;' language plpgsql;

So far this looks good to me, looking at how you implemented upgrade_sl_node  
in -HEAD, i see you aren't using this, nor can I find anyware in slonik or 
the like where upgrade_sl_node is called when an UPGRADE FUNCTIONS gets 
issued. I missing something stupid here ?

-- 
Darcy Buskermolen
Wavefire Technologies Corp.
ph: 250.717.0200
fx:  250.763.1759
http://www.wavefire.com