pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedwriting a dynamic sql
2+ messages / 2 participants
[nested] [flat]
* writing a dynamic sql
@ 2004-02-10 09:48 Senthil Kumar S <ssakkaravel@ivesia.com>
2004-02-15 19:53 ` Re: writing a dynamic sql Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 2+ messages in thread
From: Senthil Kumar S @ 2004-02-10 09:48 UTC (permalink / raw)
To: pgsql-sql
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.
Pls shed some light.
Regards
kumar
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: writing a dynamic sql
2004-02-10 09:48 writing a dynamic sql Senthil Kumar S <ssakkaravel@ivesia.com>
@ 2004-02-15 19:53 ` Tomasz Myrta <jasiek@klaster.net>
0 siblings, 0 replies; 2+ messages in thread
From: Tomasz Myrta @ 2004-02-15 19:53 UTC (permalink / raw)
To: Senthil Kumar S <ssakkaravel@ivesia.com>; +Cc: pgsql-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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-02-15 19:53 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-10 09:48 writing a dynamic sql Senthil Kumar S <ssakkaravel@ivesia.com>
2004-02-15 19:53 ` Tomasz Myrta <jasiek@klaster.net>
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