Re: Dynamic DDL - Mailing list pgsql-general

On Apr 16, 6:24 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> "Ketema" <ket...@gmail.com> writes:
> > I have an example were I have to build a string in the below manner:
> > values (' || new.tpv_success_id || ',''' || new.order_date || ''',' ||
> > new.tpv_id || ',' || new.ver_code || ',''' || new.agent_name || ''','
> > || new.agent_id || ','
> >            || new.acct_id || ',''' || new.first_name || ''',''' ||
> > new.last_name || ''',''' || new.ssn || ''',''' ||
> > coalesce(new.dl_number,'') || ''',''' || coalesce(new.spouse_name, '')
> > || ''',''' || coalesce(new.spouse_ssn,'') || ''',''' etc...
>
> This looks to me like you're simply willfully ignoring the easy path.
> There's nothing there that wouldn't work just as well without EXECUTE,
> viz
>
> values (new.tpv_success_id, new.order_date, new.tpv_id, new.ver_code,
> new.agent_name, new.agent_id, new.acct_id, new.first_name,
> new.last_name, new.ssn, new.dl_number, new.spouse_name, new.spouse_ssn,
> etc...
>
>                         regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

I am sorry for being lazy Tom....This part of the dynamix statement
your right is simple and would work.

ITs actuall in the the beginning....  INSERT INTO _dynamic_table....
Its substituting the table name for the insert that does not work and
I was wondering the technical reasons behind that.  I had thought
about a rule, but on a rule the table has to already exist in order to
do an instead of insert.  My purpose is I am trying to implement
partitioning.  I included the full trigger function below.  The
function does work as is, my only complaint is that on the columns I
have to coalesce i get '' (null string) inserted instead of an actual
null and this has made me have to make some columns text or varchar
instead of numeric or other data types. (for example cancel_date
should be a date type, but if this comes thorugh as null i have to
coalesce it or the whole string becomes null, and '' is not a valid
date type so I had to make the table column a varchar)

CREATE OR REPLACE FUNCTION frontier.order_details_partitioner()
  RETURNS "trigger" AS
$BODY$
declare
    _month text;
    _year text;
    _schema text;
    _table text;
    _table_exists text;
    _sql text;
begin
    _month := (select trim(to_char(new.order_date, 'month')));
    _year := (select trim(to_char(new.order_date, 'yyyy')));
    _schema := 'frontier';
    _table := 'order_details_' || _month || '_' || _year;
    _table_exists := (select schemaname || '.' || tablename from
pg_tables
        where schemaname = _schema and tablename = _table);
    if _table_exists is null then
        _sql := 'create table ' || _schema || '.' || _table || ' (CONSTRAINT
"C_partition_rule" CHECK ( trim(to_char(order_date, ''month'')) = '''
|| _month || ''' and trim(to_char(order_date, ''yyyy'')) = '''
        || _year || ''')) inherits (frontier.order_details);';
raise notice '%', _sql;
        execute(_sql);
    end if;
    _sql := 'insert into ' || _schema || '.' || _table ||
' (tpv_success_id, order_date, tpv_id, ver_code,
            agent_name, agent_id, acct_id, first_name, last_name, ssn,
dl_number,
            spouse_name, spouse_ssn, day_phone, evening_phone,
svc_address,
            svc_city, svc_state, svc_zip, billing_address,
billing_city,
            billing_state, billing_zip, order_number, order_status,
provisioned_date,
            promotion, products, data_requirement_titles,
data_requirement_values, cancel_date,
            cancel_note, issue_notes, has_dish, has_dish_billing_info,
dish_order_number,
            dish_install_date, dish_customer_contacted, personnel_id,
call_id,
           marketer_division_id, existing_status, app_id) values (' ||
new.tpv_success_id || ',''' || new.order_date || ''',' || new.tpv_id
|| ',' || new.ver_code || ',''' || new.agent_name || ''',' ||
new.agent_id || ','
        || new.acct_id || ',''' || new.first_name || ''',''' ||
new.last_name || ''',''' || new.ssn || ''',''' ||
coalesce(new.dl_number,'') || ''',''' || coalesce(new.spouse_name, '')
|| ''',''' || coalesce(new.spouse_ssn,'') || ''','''
        || new.day_phone || ''',''' || coalesce(new.evening_phone,'') ||
''',''' || new.svc_address || ''',''' || new.svc_city || ''',''' ||
new.svc_state || ''',''' || new.svc_zip || ''',''' ||
new.billing_address || ''',''' || new.billing_city || ''','''
        || new.billing_state || ''',''' || new.billing_zip || ''',''' ||
coalesce(new.order_number,'') || ''',''' || new.order_status ||
''',''' || coalesce(new.provisioned_date,'') || ''',''' ||
coalesce(new.promotion,'') || ''',''' || coalesce(new.products,'') ||
''',''' || coalesce(new.data_requirement_titles,'') || ''','''
        || coalesce(new.data_requirement_values,'') || ''',''' ||
coalesce(new.cancel_date,'') || ''',''' ||
coalesce(new.cancel_note,'') || ''',''' ||
coalesce(new.issue_notes,'') || ''',' || case when new.has_dish is
true then 'true' else 'false' end || ',''' ||
new.has_dish_billing_info || ''',''' ||
coalesce(new.dish_order_number,'') || ''','''
        || coalesce(new.dish_install_date, '') || ''',''' ||
new.dish_customer_contacted || ''',' || new.personnel_id || ',' ||
new.call_id || ',' || new.marketer_division_id || ',''' ||
coalesce(new.existing_status, '') || ''',' || new.app_id || ');';
raise notice '%', _sql;
    execute(_sql);
    return null;
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION frontier.order_details_partitioner() OWNER TO
all_users;
COMMENT ON FUNCTION frontier.order_details_partitioner() IS 'This
function redirects inserts into order_details into the appropriate
child table, creating it if necessary.  Child tables are kept my
month_year ex:
order_details_april_2007';