Re: Dynamic DDL - Mailing list pgsql-general

On Apr 17, 11:19 am, Ketema <ket...@gmail.com> wrote:
> On Apr 17, 7:35 am, Ketema <ket...@gmail.com> wrote:
>
>
>
> > 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';
>
> One of the other problems I have with building a string for execution
> is that certain data types have no operator to concantenate to a
> string.  Array for example.  Even though when you select and array you
> get a nice array notation {val, "quoted val", val} and if you put
> single quotes around the same thing pg happily understands that it is
> an array.  Yet try to concatenate a varchar[] column in a function
> such as mine and you get:
>
> operator does not exist: text || character varying[]
>
> and you can't explicitly cast and array column to text or varchar
> either.  Does any one have a custom operator that wlll do that?

Please see http://pgsql.privatepaste.com/291tTsTeGp for a solutions I
came up with.  I would appreciate feedback.

Thanks