Message-ID: <4F704423.5040706@comodo.com>
Date: Mon, 26 Mar 2012 15:55:39 +0530
From: Robins Tharakan <robins.tharakan@comodo.com>
User-Agent: Mozilla/5.0 (X11; Linux i686;
	rv:10.0.3) Gecko/20120314 Thunderbird/10.0.3
MIME-Version: 1.0
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: MS-SQL to PostgreSql
References: <1332748147.44735.YahooMailNeo@web121604.mail.ne1.yahoo.com>
	<4F703EEE.2080601@comodo.com>
	<1332757092.74290.YahooMailNeo@web121605.mail.ne1.yahoo.com>
In-Reply-To: <1332757092.74290.YahooMailNeo@web121605.mail.ne1.yahoo.com>
Content-Type: multipart/signed; protocol="application/pkcs7-signature";
	micalg=sha1; boundary="------------ms010206000002000906010801"

This is a cryptographically signed message in MIME format.

--------------ms010206000002000906010801
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable

Hi Rehan,

p.s.: Please send all mails to list, so that all can see the response.

The function returns for all, probably because you see to be RETURN'ing=20
the values before any processing. In the MSSQL you generate the SQL in a =

string and then execute the SQL. However, for some reason you seem to be =

RETURNing the output immediately after the 'IF center_distance IS NULL'=20
condition.

Probably the RETURN needs to happen 'after' all the chr checks.

--
Robins

On 03/26/2012 03:48 PM, Rehan Saleem wrote:
> hi ,
> i have tried this but it is not working correctly , when i pass it a
> value which is present in the chr column chr1 it show all chr values no=
t
> only the chr1 values .
>
> CREATE OR REPLACE FUNCTION getsitesbytfid(user_datadetailid int, kb_id
> int,bp_overlap int,chr_ varchar ,centre_distance int)
> RETURNS table(chr__u varchar,start__u int,end__u int,region_size_u
> int,chr__kb varchar,start__kb int ,end__kb int,region_size_kb
> int,bp__overlap int,centre__distance int)
> as
> $BODY$
> DECLARE sql varchar ;
> BEGIN
>
>
> if (centre_distance is NULL) THEN
> centre_distance :=3D 1;
> end if;
>
>
> return query select chr_u, start_u, end_u, regionsize_u, chr_kb,
> start_kb, end_kb, regionsize_kb,
> bpoverlap, centredistance from vwchrcomparesites where userdatadetailid=
=3D
> + user_datadetailid
> and bpoverlap>=3D + bp_overlap
> and kbid=3D + kb_id ;
>
> if chr_<>'all' and COALESCE(chr_,'')<>'' then
> chr_:=3Dchr_ ;
> end if;
> if centre_distance IS NULL THEN
> centre_distance :=3D ' and (centredistance<=3D' + centre_distance + ' o=
r ' +
> centre_distance + '=3D1) '
> || ' order by chr_u, start_u';
>
>
> --exec sql;
>
> end if;
>
> END;
> $BODY$
> language plpgsql;
> -----------------------------------------------------------------------=
-
> *From:* Robins Tharakan <robins.tharakan@comodo.com>
> *To:* pgsql-sql@postgresql.org
> *Sent:* Monday, March 26, 2012 3:03 PM
> *Subject:* Re: [SQL] MS-SQL to PostgreSql
>
> Hi,
>
> What all have you tried?
> What are you getting stuck at?
> Let us see some samples and may be someone could provide some input.
>
> --
> Robins
>
> On 03/26/2012 01:19 PM, Rehan Saleem wrote:
>  > hi,
>  > i am trying to convert this mssql store procedure to postgresql func=
tion
>  > but it is not giving me the desired output ,this function is returni=
ng a
>  > table and you dont need to worry about what it is returning all i
>  > concern the body part of the function how to transform the ms-sql co=
de
>  > into postgresql code, i shall be very thankful if some one convert t=
his
>  > procedure into function,
>  >
>  > ALTER PROCEDURE [dbo].[sp_GetSitesByTFID]
>  > @UserDataDetailId varchar(50),
>  > @KBId varchar(50),
>  > @bpOverlap varchar(50),
>  > @Chr varchar(50),
>  > @CentreDistance varchar(50)=3D'',
>  > @TotalMatched varchar(50) output
>  > AS
>  > BEGIN
>  >
>  > DECLARE @sql nvarchar(500);
>  >
>  >
>  > if (@CentreDistance=3D'')
>  > set @CentreDistance =3D 1
>  > set @sql =3D 'select Chr_U, Start_U, End_U, RegionSize_U, Chr_KB,
>  > Start_KB, End_KB, RegionSize_KB, '
>  > set @sql +=3D ' bpOverlap, CentreDistance from vwChrCompareSiteswher=
e
>  > UserDataDetailId=3D' + @UserDataDetailId
>  > set @sql +=3D ' and bpOverlap>=3D' + @bpOverlap
>  > set @sql +=3D ' AND KBId=3D' + @KBId
>  > if @Chr<>'All' and ISNULL(@Chr,'')<>''
>  > set @sql +=3D ' AND Chr_U=3D''' + @Chr +''''
>  > if (@CentreDistance<>'')
>  > set @sql +=3D ' AND (CentreDistance<=3D' + @CentreDistance + ' or ' =
+
>  > @CentreDistance + '=3D1) '
>  > set @sql +=3D ' Order by Chr_U, Start_U'
>  >
>  > exec(@sql)
>  > set @TotalMatched =3D @@ROWCOUNT
>  > END
>  >
>
>
>


--------------ms010206000002000906010801
Content-Type: application/pkcs7-signature; name="smime.p7s"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="smime.p7s"
Content-Description: S/MIME Cryptographic Signature

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIPkTCC
BIowggNyoAMCAQICECf06hH0eobEbp27bqkXBwcwDQYJKoZIhvcNAQEFBQAwbzELMAkGA1UE
BhMCU0UxFDASBgNVBAoTC0FkZFRydXN0IEFCMSYwJAYDVQQLEx1BZGRUcnVzdCBFeHRlcm5h
bCBUVFAgTmV0d29yazEiMCAGA1UEAxMZQWRkVHJ1c3QgRXh0ZXJuYWwgQ0EgUm9vdDAeFw0w
NTA2MDcwODA5MTBaFw0yMDA1MzAxMDQ4MzhaMIGuMQswCQYDVQQGEwJVUzELMAkGA1UECBMC
VVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNUIE5l
dHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2MDQGA1UEAxMtVVRO
LVVTRVJGaXJzdC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsMIIBIjANBgkqhkiG
9w0BAQEFAAOCAQ8AMIIBCgKCAQEAsjmFpPJ9q0E7YkY3rs3BYHW8OWX5ShpHornMSMxqmNVN
NRm5pELlzkniii8efNIxB8dOtINknS4p1aJkxIW9hVE1eaROaJB7HHqkkqgX8pgV8pPMyaQy
lbsMTzC9mKALi+VuG6JG+ni8om+rWV6lL8/K2m2qL+usobNqqrcuZzWLeeEeaYji5kbNoKXq
vgvOdjp6Dpvq/NonWz1zHyLmSGHGTPNpsaguG7bUMSAsvIKKjqQOpdeJQ/wWWq8dcdcRWdq6
hw2v+vPhwvCkxWeM1tZUOt4KpLoDd7NlyP0e03RiqhjKaJMeoYV+9Udly/hNVyh00jT/MLbu
9mIwFIws6wIDAQABo4HhMIHeMB8GA1UdIwQYMBaAFK29mHo0tCb3+sQmVO8DveAky1QaMB0G
A1UdDgQWBBSJgmd9xJ0mcABLtFBIfN49rgRufTAOBgNVHQ8BAf8EBAMCAQYwDwYDVR0TAQH/
BAUwAwEB/zB7BgNVHR8EdDByMDigNqA0hjJodHRwOi8vY3JsLmNvbW9kb2NhLmNvbS9BZGRU
cnVzdEV4dGVybmFsQ0FSb290LmNybDA2oDSgMoYwaHR0cDovL2NybC5jb21vZG8ubmV0L0Fk
ZFRydXN0RXh0ZXJuYWxDQVJvb3QuY3JsMA0GCSqGSIb3DQEBBQUAA4IBAQAZ2IkRbyispgCi
54fBm5AD236hEv0e8+LwAamUVEJrmgnEoG3XkJIEA2Z5Q3H8+G+v23ZF4jcaPd3kWQR4rBz0
g0bzes9bhHIt5UbBuhgRKfPLSXmHPLptBZ2kbWhPrXIUNqi5sf2/z3/wpGqUNVCPz4FtVbHd
WTBK322gnGQfSXzvNrv042n0+DmPWq1LhTq3Du3Tzw1EovsEv+QvcI4l+1pUBrPQxLxtjftz
Mizpm4QkLdZ/kXpoAlAfDj9N6cz1u2fo3BwuO/xOzf4CjuOoEwqlJkRl6RDyTVKnrtw+ymsy
XEFs/vVdoOr/0fqbhlhtPZZH5f4ulQTCAMyOofK7MIIFGjCCBAKgAwIBAgIQbRnqpxlPajMi
5iIyeqpx3jANBgkqhkiG9w0BAQUFADCBrjELMAkGA1UEBhMCVVMxCzAJBgNVBAgTAlVUMRcw
FQYDVQQHEw5TYWx0IExha2UgQ2l0eTEeMBwGA1UEChMVVGhlIFVTRVJUUlVTVCBOZXR3b3Jr
MSEwHwYDVQQLExhodHRwOi8vd3d3LnVzZXJ0cnVzdC5jb20xNjA0BgNVBAMTLVVUTi1VU0VS
Rmlyc3QtQ2xpZW50IEF1dGhlbnRpY2F0aW9uIGFuZCBFbWFpbDAeFw0xMTA0MjgwMDAwMDBa
Fw0yMDA1MzAxMDQ4MzhaMIGTMQswCQYDVQQGEwJHQjEbMBkGA1UECBMSR3JlYXRlciBNYW5j
aGVzdGVyMRAwDgYDVQQHEwdTYWxmb3JkMRowGAYDVQQKExFDT01PRE8gQ0EgTGltaXRlZDE5
MDcGA1UEAxMwQ09NT0RPIENsaWVudCBBdXRoZW50aWNhdGlvbiBhbmQgU2VjdXJlIEVtYWls
IENBMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAkoSEW0tXmNReL4uk4UDIo1NY
X2Zl8TJO958yfVXQeExVt0KU4PkncQfFxmmkuTLE8UAakMwnVmJ/F7Vxaa7lIBvky2NeYMqi
QfZq4aP/uN8fSG1lQ4wqLitjOHffsReswtqCAtbUMmrUZ28gE49cNfrlVICv2HEKHTcKAlBT
bJUdqRAUtJmVWRIx/wmi0kzcUtve4kABW0ho3cVKtODtJB86r3FfB+OsvxQ7sCVxaD30D9YX
WEYVgTxoi4uDD216IVfmNLDbMn7jSuGlUnJkJpFOpZIP/+CxYP0ab2hRmWONGoulzEKbm30i
Y9OpoPzOnpDfRBn0XFs1uhbzp5v/wQIDAQABo4IBSzCCAUcwHwYDVR0jBBgwFoAUiYJnfcSd
JnAAS7RQSHzePa4Ebn0wHQYDVR0OBBYEFHoTTgB0W8Z4Y2QnwS/ioFu8ecV7MA4GA1UdDwEB
/wQEAwIBBjASBgNVHRMBAf8ECDAGAQH/AgEAMBEGA1UdIAQKMAgwBgYEVR0gADBYBgNVHR8E
UTBPME2gS6BJhkdodHRwOi8vY3JsLnVzZXJ0cnVzdC5jb20vVVROLVVTRVJGaXJzdC1DbGll
bnRBdXRoZW50aWNhdGlvbmFuZEVtYWlsLmNybDB0BggrBgEFBQcBAQRoMGYwPQYIKwYBBQUH
MAKGMWh0dHA6Ly9jcnQudXNlcnRydXN0LmNvbS9VVE5BZGRUcnVzdENsaWVudF9DQS5jcnQw
JQYIKwYBBQUHMAGGGWh0dHA6Ly9vY3NwLnVzZXJ0cnVzdC5jb20wDQYJKoZIhvcNAQEFBQAD
ggEBAIXWvnhXVW0zf0RS/kLVBqgBA4CK+w2y/Uq/9q9BSfUbWsXSrRtzbj7pJnzmTJjBMCjf
y/tCPKElPgp11tA9OYZm0aGbtU2bb68obB2v5ep0WqjascDxdXovnrqTecr+4pEeVnSy+I3T
4ENyG+2P/WA5IEf7i686ZUg8mD2lJb+972DgSeUWyOs/Q4Pw4O4NwdPNM1+b0L1garM7/vrU
yTo8H+2b/5tJM75CKTmD7jNpLoKdRU2oadqAGx490hpdfEeZpZsIbRKZhtZdVwcbpzC+S0lE
uJB+ytF5OOu0M/qgOl0mWJ5hVRi0IdWZ1eBDQEIwvuql55TSsP7zdfl/bucwggXhMIIEyaAD
AgECAhBfecn+BRfRwsBoBq9kPMYvMA0GCSqGSIb3DQEBBQUAMIGTMQswCQYDVQQGEwJHQjEb
MBkGA1UECBMSR3JlYXRlciBNYW5jaGVzdGVyMRAwDgYDVQQHEwdTYWxmb3JkMRowGAYDVQQK
ExFDT01PRE8gQ0EgTGltaXRlZDE5MDcGA1UEAxMwQ09NT0RPIENsaWVudCBBdXRoZW50aWNh
dGlvbiBhbmQgU2VjdXJlIEVtYWlsIENBMB4XDTExMDgzMDAwMDAwMFoXDTE0MDgyOTIzNTk1
OVowgcAxDjAMBgNVBBETBTA3MzEwMRowGAYDVQQKExFDb21vZG8gR3JvdXAgSW5jLjEdMBsG
A1UECRMUNTI1IFdhc2hpbmd0b24gQmx2ZC4xCzAJBgNVBAgTAk5KMRQwEgYDVQQHEwtKZXJz
ZXkgQ2l0eTELMAkGA1UEBhMCVVMxGDAWBgNVBAMTD1JvYmlucyBUaGFyYWthbjEpMCcGCSqG
SIb3DQEJARYacm9iaW5zLnRoYXJha2FuQGNvbW9kby5jb20wggEiMA0GCSqGSIb3DQEBAQUA
A4IBDwAwggEKAoIBAQCckvPmEZ6upE2ch9jYGwz+P/qRpSqy5EYOQ0W7T2k6SZJ8c6MosxCR
Dhp3wIVNjgKTrOh5fsDZnrFm8ct+QGOXTscKi0KG12HSmZJW8ACxOXCaEGrjKX8xL629Gf9w
JEnoRKrkm4lvZV7j9LTxhSfMLE+CQdakQaNM7+C9xZvazFU9VdmiB2NX1OAR5bBsJcJk4vWv
55buaVqRQqTTE7J7N7ZHPPYeNA2TKD6a6q/2gwpi5OUKXK13x/2T5V4STYcN5rVjO6uJik+y
ZUf5OyAuGIt50Y3IPVLHOpn7muh7/Z9hV+6OyjnJEu00emZjT/f5KbtWUJh5itJgKoln+Csj
AgMBAAGjggIAMIIB/DAfBgNVHSMEGDAWgBR6E04AdFvGeGNkJ8Ev4qBbvHnFezAdBgNVHQ4E
FgQUBvR8jLJLDqVVQXdVWsc/n7XQK9AwDgYDVR0PAQH/BAQDAgWgMAwGA1UdEwEB/wQCMAAw
HQYDVR0lBBYwFAYIKwYBBQUHAwQGCCsGAQUFBwMCMEYGA1UdIAQ/MD0wOwYMKwYBBAGyMQEC
AQMFMCswKQYIKwYBBQUHAgEWHWh0dHBzOi8vc2VjdXJlLmNvbW9kby5uZXQvQ1BTMFcGA1Ud
HwRQME4wTKBKoEiGRmh0dHA6Ly9jcmwuY29tb2RvY2EuY29tL0NPTU9ET0NsaWVudEF1dGhl
bnRpY2F0aW9uYW5kU2VjdXJlRW1haWxDQS5jcmwwgYgGCCsGAQUFBwEBBHwwejBSBggrBgEF
BQcwAoZGaHR0cDovL2NydC5jb21vZG9jYS5jb20vQ09NT0RPQ2xpZW50QXV0aGVudGljYXRp
b25hbmRTZWN1cmVFbWFpbENBLmNydDAkBggrBgEFBQcwAYYYaHR0cDovL29jc3AuY29tb2Rv
Y2EuY29tMFEGA1UdEQRKMEigKgYKKwYBBAGCNxQCA6AcDBpyb2JpbnMudGhhcmFrYW5AY29t
b2RvLmNvbYEacm9iaW5zLnRoYXJha2FuQGNvbW9kby5jb20wDQYJKoZIhvcNAQEFBQADggEB
AHoaVJ/H1rq8vKCcgKtpJjc0V4zJ+MJpQo0piYBxHIn8oHYv2WccGfzeR+2r8HfEbkwuCr21
ILj/XKfcT4JCxbNNTNMVLnyYgC89meCKHdS1Huj8eN39oLulImQ1Lbfbklm8wS2ox1iq7ucO
RIA3Dyz+fX/HNUlLRL+hevMXz4IvmcKCC/15Pd2xBuXfdulhzpZiCiLNDqfFwAYa9gVxruDH
W2IMgU/Qfg7W9revhOTjEiBXDMKq5YXXWzr3Wjyk5J2vmfaX35JCIhL8a/64io6kAmCTh1Tj
fe9Wwo5zs2A8yKOBeZToA07+koyM+NRpREvDULLjamrIGz48ghvgSMsxggQMMIIECAIBATCB
qDCBkzELMAkGA1UEBhMCR0IxGzAZBgNVBAgTEkdyZWF0ZXIgTWFuY2hlc3RlcjEQMA4GA1UE
BxMHU2FsZm9yZDEaMBgGA1UEChMRQ09NT0RPIENBIExpbWl0ZWQxOTA3BgNVBAMTMENPTU9E
TyBDbGllbnQgQXV0aGVudGljYXRpb24gYW5kIFNlY3VyZSBFbWFpbCBDQQIQX3nJ/gUX0cLA
aAavZDzGLzAJBgUrDgMCGgUAoIICODAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqG
SIb3DQEJBTEPFw0xMjAzMjYxMDI1MzlaMCMGCSqGSIb3DQEJBDEWBBTqmHC9+ik60siE1M4c
hPuUAEV2djBfBgkqhkiG9w0BCQ8xUjBQMAsGCWCGSAFlAwQBAjAKBggqhkiG9w0DBzAOBggq
hkiG9w0DAgICAIAwDQYIKoZIhvcNAwICAUAwBwYFKw4DAgcwDQYIKoZIhvcNAwICASgwgbkG
CSsGAQQBgjcQBDGBqzCBqDCBkzELMAkGA1UEBhMCR0IxGzAZBgNVBAgTEkdyZWF0ZXIgTWFu
Y2hlc3RlcjEQMA4GA1UEBxMHU2FsZm9yZDEaMBgGA1UEChMRQ09NT0RPIENBIExpbWl0ZWQx
OTA3BgNVBAMTMENPTU9ETyBDbGllbnQgQXV0aGVudGljYXRpb24gYW5kIFNlY3VyZSBFbWFp
bCBDQQIQX3nJ/gUX0cLAaAavZDzGLzCBuwYLKoZIhvcNAQkQAgsxgauggagwgZMxCzAJBgNV
BAYTAkdCMRswGQYDVQQIExJHcmVhdGVyIE1hbmNoZXN0ZXIxEDAOBgNVBAcTB1NhbGZvcmQx
GjAYBgNVBAoTEUNPTU9ETyBDQSBMaW1pdGVkMTkwNwYDVQQDEzBDT01PRE8gQ2xpZW50IEF1
dGhlbnRpY2F0aW9uIGFuZCBTZWN1cmUgRW1haWwgQ0ECEF95yf4FF9HCwGgGr2Q8xi8wDQYJ
KoZIhvcNAQEBBQAEggEAgdBS71xcBKIzBVc0x3hug39uzMvwBHVb1mZWhvdKW3+Y5ltqYRIK
9xoq0D+GwpSLi39bsRQl8l0X/6PE39kMtWWeEVphqi32LefnziR4XRIU754M6W5XU59gbP1k
kUdunE3giiZ15E63uvhnLyQlTgmchbolrdanc7jEujBfbpH4nqhSM3AUh2n46adXim0WcbFk
LdOK98JYpKOGmvKIkLI0fi58mdLq1XuBOF0rJCln2w1HaaTkBEueMPEq4jmJ3p+CiW1xYfoS
hlgNl3mvAr3dZzmlsML8JFVeWfCZFfP0lhgTdOlPZE+m2DP6s5Tz7Tp6v71jFMzwAwl/rYO1
ygAAAAAAAA==
--------------ms010206000002000906010801--