From ssakkaravel@ivesia.com Tue Feb  10 09:48:20 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id DB75ED1D1CD
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 10 Feb 2004 09:48:20 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 76068-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 10 Feb 2004 05:48:05 -0400 (AST)
Received: from chn-dns.primus-india.net (chn-dns.primus-india.net
	[203.196.153.7])
	by svr1.postgresql.org (Postfix) with ESMTP id 4570FD1B83D
	for <pgsql-sql@postgresql.org>; Tue, 10 Feb 2004 05:48:01 -0400 (AST)
Received: from IVESIA67 (ptil-138-190-chn.primus-india.net [203.196.190.138]
	(may be forged))
	by chn-dns.primus-india.net (8.12.8/8.12.8) with SMTP id i1A8xp1x016110
	for <pgsql-sql@postgresql.org>; Tue, 10 Feb 2004 14:29:52 +0530
Message-ID: <01b201c3efbb$097a2af0$7502a8c0@hdsc.com>
From: "Senthil Kumar S" <ssakkaravel@ivesia.com>
To: "psql" <pgsql-sql@postgresql.org>
Subject: writing a dynamic sql 
Date: Tue, 10 Feb 2004 15:18:27 +0530
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_01AF_01C3EFE9.2230FC80"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=BAYES_44,
	HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200402/176
X-Sequence-Number: 16866

This is a multi-part message in MIME format.

------=_NextPart_000_01AF_01C3EFE9.2230FC80
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Dear friends,

I am having an lengthy SQL, which will be called every conditions of if...e=
lse statement. I tried with passing that via a string and execute it.
But I got error.

v_sql :=3D
  '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

------=_NextPart_000_01AF_01C3EFE9.2230FC80
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY style=3D"COLOR: #0000ff; FONT-FAMILY: Arial" bgColor=3D#ffffff>
<DIV>Dear friends,<BR><BR>I am having an lengthy SQL, which will be called =
every=20
conditions of if...else statement. I tried with passing that via a string a=
nd=20
execute it.<BR>But I got error.<BR><FONT size=3D2></FONT></DIV>
<DIV>v_sql :=3D<BR>&nbsp; 'INSERT INTO activities(<BR>&nbsp;&nbsp;&nbsp;=20
activity_id,<BR>&nbsp;&nbsp;&nbsp; parent_activity_id,<BR>&nbsp;&nbsp;&nbsp=
;=20
activity_type_id,<BR>&nbsp;&nbsp;&nbsp; subject,<BR>&nbsp;&nbsp;&nbsp;=20
description,<BR>&nbsp;&nbsp;&nbsp; category_id,<BR>&nbsp;&nbsp;&nbsp;=20
priority_id,<BR>&nbsp;&nbsp;&nbsp; activity_start_time,<BR>&nbsp;&nbsp;&nbs=
p;=20
activity_end_time,<BR>)<BR>&nbsp;VALUES&nbsp;&nbsp; (<BR>&nbsp;&nbsp;&nbsp;=
=20
NEXTVAL(\'seq_activities\'),<BR>&nbsp;&nbsp;&nbsp;=20
rec_recurrence.activity_id,<BR>&nbsp;&nbsp;&nbsp;=20
rec_activity.activity_type_id,<BR>&nbsp;&nbsp;&nbsp;=20
rec_activity.subject,<BR>&nbsp;&nbsp;&nbsp;=20
rec_activity.description,<BR>&nbsp;&nbsp;&nbsp;=20
rec_activity.category_id,<BR>&nbsp;&nbsp;&nbsp;=20
rec_activity.priority_id,<BR>&nbsp;&nbsp;&nbsp;=20
rec_activity.activity_start_time,<BR>&nbsp;&nbsp;&nbsp;=20
rec_activity.activity_end_time<BR>);';<BR><BR>&nbsp; execute v_sql;<BR><BR>=
I got=20
an error saying that the record variable which I declared earlier is<BR>not=
=20
having reference (or it does not exists).<BR><BR>Any suggestion to pack thi=
s=20
inside a dynamic function and call it inside<BR>many if conditions.<BR><BR>=
Pls=20
shed some light.<BR><BR>Regards<BR>kumar</DIV>
<P></P></BODY></HTML>

------=_NextPart_000_01AF_01C3EFE9.2230FC80--




From jasiek@klaster.net Sun Feb  15 19:50:58 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 76BE8D1DD7B
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 15 Feb 2004 19:50:58 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 72492-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 15 Feb 2004 15:50:58 -0400 (AST)
Received: from serwer.skawsoft.com.pl (skawina.eu.org [80.48.213.66])
	by svr1.postgresql.org (Postfix) with ESMTP id 3A456D1DD79
	for <pgsql-sql@postgresql.org>; Sun, 15 Feb 2004 15:50:56 -0400 (AST)
Received: from klaster.net (backbone.zab.citynet.pl [212.244.6.69])
	by serwer.skawsoft.com.pl (Postfix) with ESMTP
	id 504E22B3CB; Sun, 15 Feb 2004 20:50:39 +0100 (CET)
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
X-Accept-Language: pl, en-us, en
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
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/184
X-Sequence-Number: 16874

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