Message-ID: <402FCE48.4010902@klaster.net>
Date: Sun, 15 Feb 2004 20:53:44 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; pl-PL;
 rv:1.6) Gecko/20040113
MIME-Version: 1.0
To: Senthil Kumar S <ssakkaravel@ivesia.com>
Cc: psql <pgsql-sql@postgresql.org>
Subject: Re: writing a dynamic sql
References: <01b201c3efbb$097a2af0$7502a8c0@hdsc.com>
In-Reply-To: <01b201c3efbb$097a2af0$7502a8c0@hdsc.com>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 8bit

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