From: Tomasz Myrta <jasiek@klaster.net>
To: Senthil Kumar S <ssakkaravel@ivesia.com>
Cc: psql <pgsql-sql@postgresql.org>
Subject: Re: writing a dynamic sql
Date: Sun, 15 Feb 2004 20:53:44 +0100
Message-ID: <402FCE48.4010902@klaster.net> (raw)
In-Reply-To: <01b201c3efbb$097a2af0$7502a8c0@hdsc.com>
References: <01b201c3efbb$097a2af0$7502a8c0@hdsc.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jasiek@klaster.net, ssakkaravel@ivesia.com
Subject: Re: writing a dynamic sql
In-Reply-To: <402FCE48.4010902@klaster.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox