Re: Dynamic DDL

On Apr 16, 4:47 pm, klep(dot)(dot)(dot)(at)svana(dot)org (Martijn van Oosterhout) wrote:
> On Mon, Apr 16, 2007 at 01:37:43PM -0700, Ketema wrote:
> > create or replace function schema.insert_function(_schema text, _table
> > text) returns integer
>
> <snip>
>
> > set search_path to _schema;
> > insert into _table (columns) values(vals);
>
> <snip>
>
> > is there any whay to get the functionality of above to work with out
> > have to build a string and use the execute function?
>

> No.
>
> Have a nice day,
> --
> Martijn van Oosterhout <klep(dot)(dot)(dot)(at)svana(dot)org> http://svana.org/kleptog/
>
> > From each according to his ability. To each according to his ability to litigate.
>
>
>
> signature.asc
> 1KDownload

Is there a technical reason why not? I'd love to understand why.
This would be really usefull if it were possible.

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...

each column that i know can possibly be null I have to wrap in
coalesce so that the null does not cause the whole string to be null,
and therefore unable to be executed. This is annoying and also causes
be to be unable to insert nulls and in some cases it would be nice to
have them.

Thanks