Re: writing a dynamic sql

Dnia 2004-02-10 10:48, Użytkownik Senthil Kumar S napisał:
> Dear friends,
>
> I am having an lengthy SQL, which will be called every conditions of
> if...else statement. I tried with passing that via a string and execute it.
> But I got error.
> v_sql :=
> 'INSERT INTO activities(
> activity_id,
> parent_activity_id,
> activity_type_id,
> subject,
> description,
> category_id,
> priority_id,
> activity_start_time,
> activity_end_time,
> )
> VALUES (
> NEXTVAL(\'seq_activities\'),
> rec_recurrence.activity_id,
> rec_activity.activity_type_id,
> rec_activity.subject,
> rec_activity.description,
> rec_activity.category_id,
> rec_activity.priority_id,
> rec_activity.activity_start_time,
> rec_activity.activity_end_time
> );';
>
> execute v_sql;
>
> I got an error saying that the record variable which I declared earlier is
> not having reference (or it does not exists).
>
> Any suggestion to pack this inside a dynamic function and call it inside
> many if conditions.

Your v_sql variable *must* contain static text only. It can't have any
references to variables.

Here is little example how your query should look like:
...
VALUES (
NEXTVAL(\'seq_activities\'), '' || rec_recurrence.activity_id::text
|| '','' || rec_activity.activity_type_id::text
...

After this v_sql will be:
VALUES (
NEXTVAL(\'seq_activities\'),1,2,...

Regards,
Tomasz Myrta