From sgnerd@yahoo.com.sg Mon Oct  6 13:08:03 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 5B160D1B592
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  6 Oct 2003 13:08:03 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 55732-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  6 Oct 2003 10:07:16 -0300 (ADT)
Received: from smtp101.mail.sc5.yahoo.com (smtp101.mail.sc5.yahoo.com
	[216.136.174.139])
	by svr1.postgresql.org (Postfix) with SMTP id C9CE9D1B51E
	for <pgsql-sql@postgresql.org>; Mon,  6 Oct 2003 10:07:11 -0300 (ADT)
Received: from ptil-138-190-chn.primus-india.net (HELO IVESIA67)
	(sgnerd@203.196.190.138 with login)
	by smtp.mail.vip.sc5.yahoo.com with SMTP; 6 Oct 2003 13:00:11 -0000
Message-ID: <017801c38c0a$7e4bbc00$7502a8c0@hdsc.com>
Reply-To: "Kumar" <sgnerd@yahoo.com.sg>
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "psql" <pgsql-sql@postgresql.org>
Subject: Converting Query from MS SQL
Date: Mon, 6 Oct 2003 18:34:45 +0530
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0171_01C38C38.83B1CA30"
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.7 tagged_above=0.0 required=5.0 tests=HTML_40_50
X-Spam-Level: 
X-Archive-Number: 200310/65
X-Sequence-Number: 15465

This is a multi-part message in MIME format.

------=_NextPart_000_0171_01C38C38.83B1CA30
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Dear Friends,

I am working with Postgres 7.3.4 on RH Linux 7.2. While migrating all the S=
Ps (from MS SQL Server), I come across these lines in MS SQL Server procedu=
re.

SET @v_sql =3D "UPDATE "Schema1".employee SET rec_deleted_flag =3D 'Y' WHER=
E empid IN (" + @p_list_ids + ");"
 EXEC(@v_sql)

Actually to dynamically pass the values for the 'IN' the pass the  @p_list_=
ids (a comma separated string ) to the variable @v_sql and then execute it.

How can I change it for postgres?

Thank you very much for ur support


Regards
Kumar

------=_NextPart_000_0171_01C38C38.83B1CA30
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.1226" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY style=3D"COLOR: #0000ff; FONT-FAMILY: Arial" bgColor=3D#ffffff>
<DIV><FONT size=3D2>Dear Friends,</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>I am working&nbsp;with Postgres 7.3.4 on RH Linux 7.2. =
While=20
migrating all the SPs (from MS SQL Server), I come across these lines in MS=
 SQL=20
Server procedure.</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>SET @v_sql =3D "UPDATE "Schema1".employee&nbsp;SET=20
rec_deleted_flag =3D 'Y'&nbsp;WHERE empid&nbsp;IN (" + @p_list_ids +=20
");"<BR>&nbsp;EXEC(@v_sql)</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Actually to dynamically pass the values for the 'IN' th=
e pass=20
the&nbsp; @p_list_ids (a comma separated string ) to the variable @v_sql an=
d=20
then execute it.</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>How can I change it for postgres?</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Thank you very much for ur support</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Regards</FONT></DIV>
<DIV><FONT size=3D2>Kumar</FONT></DIV>
<P></P></BODY></HTML>

------=_NextPart_000_0171_01C38C38.83B1CA30--




From dev@archonet.com Mon Oct  6 13:53:07 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 1E2ABD1B54D
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  6 Oct 2003 13:53:07 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 69691-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  6 Oct 2003 10:52:23 -0300 (ADT)
Received: from anchor-post-31.mail.demon.net (anchor-post-31.mail.demon.net
	[194.217.242.89])
	by svr1.postgresql.org (Postfix) with ESMTP id 9AC2FD1B56E
	for <pgsql-sql@postgresql.org>; Mon,  6 Oct 2003 10:52:19 -0300 (ADT)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-31.mail.demon.net with esmtp (Exim 3.35 #1)
	id 1A6Vmc-000GMv-0V; Mon, 06 Oct 2003 14:52:18 +0100
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mainbox.archonet.com (Postfix) with ESMTP
	id 1DBDE17138; Mon,  6 Oct 2003 14:52:17 +0100 (BST)
Received: from client17.archonet.com (client17.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP
	id 6AD551681E; Mon,  6 Oct 2003 14:52:16 +0100 (BST)
From: Richard Huxton <dev@archonet.com>
To: "Kumar" <sgnerd@yahoo.com.sg>, "psql" <pgsql-sql@postgresql.org>
Subject: Re: Converting Query from MS SQL
Date: Mon, 6 Oct 2003 14:52:15 +0100
User-Agent: KMail/1.5
References: <017801c38c0a$7e4bbc00$7502a8c0@hdsc.com>
In-Reply-To: <017801c38c0a$7e4bbc00$7502a8c0@hdsc.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200310061452.15939.dev@archonet.com>
X-Virus-Scanned: by AMaViS snapshot-20020531
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200310/67
X-Sequence-Number: 15467

On Monday 06 October 2003 14:04, Kumar wrote:
> Dear Friends,
>
> I am working with Postgres 7.3.4 on RH Linux 7.2. While migrating all the
> SPs (from MS SQL Server), I come across these lines in MS SQL Server
> procedure.
>
> SET @v_sql = "UPDATE "Schema1".employee SET rec_deleted_flag = 'Y' WHERE
> empid IN (" + @p_list_ids + ");" EXEC(@v_sql)
>
> Actually to dynamically pass the values for the 'IN' the pass the 
> @p_list_ids (a comma separated string ) to the variable @v_sql and then
> execute it.
>
> How can I change it for postgres?

Something like (in plpgsql):

EXECUTE ''UPDATE "Schema1".employee SET ...'' || my_list_var || '')'';

See the manuals for more details.
-- 
  Richard Huxton
  Archonet Ltd