X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 5FD0F9DC817
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 29 Dec 2005 11:18:30 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 38716-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 29 Dec 2005 11:18:34 -0400 (AST)
X-Greylist: delayed 00:07:02.126933 by SQLgrey-
Received: from uproxy.gmail.com (uproxy.gmail.com [66.249.92.200])
	by postgresql.org (Postfix) with ESMTP id E59E09DC874
	for <pgsql-sql@postgresql.org.>; Thu, 29 Dec 2005 11:18:27 -0400 (AST)
Received: by uproxy.gmail.com with SMTP id h2so583093ugf
	for <pgsql-sql@postgresql.org.>; Thu, 29 Dec 2005 07:18:32 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:mime-version:content-type;
	b=tkcj/j3cWkzO4rllY45U21xvq0wrqI1xwOBRHxmIC9g1SbqY4cWsvLnHt7gAsCGRbYzNjQ98ktCexF/5csfoze/lVowkVtjkxWH/sRPWZNz0Ic01ZS7Cve7B2L2SrKZTIqpW8nOiiDKvrbk5/6dX68USuV3QUpiQCIeO1AsBNCM=
Received: by 10.67.15.11 with SMTP id s11mr1642079ugi;
	Thu, 29 Dec 2005 07:11:29 -0800 (PST)
Received: by 10.66.241.18 with HTTP; Thu, 29 Dec 2005 07:11:29 -0800 (PST)
Message-ID: <cca13a160512290711t42b7d544u@mail.gmail.com>
Date: Thu, 29 Dec 2005 15:11:29 +0000
From: Simon Gardner <slgard@gmail.com>
To: pgsql-sql@postgresql.org.
Subject: Problem with simple update query
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_28826_14871890.1135869089518"
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=1.333 required=5 tests=[HTML_MESSAGE=0.001,
	RCVD_IN_BL_SPAMCOP_NET=1.332]
X-Spam-Score: 1.333
X-Spam-Level: *
X-Archive-Number: 200512/247
X-Sequence-Number: 23789

------=_Part_28826_14871890.1135869089518
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi All,

Would appreciate some help with something that I think should be really eas=
y
but I've been tearing my hair out with all day.

I want to execute a query which adds a new row into a table or updates an
existing row if the id already exists.

Something like the query I would like to execute is shown below...  (I am
trying to use  Npgsql in C# incidently).

BEGIN

IF :ID < 0 THEN
    INSERT INTO myTable DEFAULT VALUES;
    :ID =3D curval(properties);

END

    UPDATE myTable SET
     type =3D :TYPE,
     address_id =3D :ADDRESS_ID,
     address_line1 =3D :ADDRESS_LINE1,
     address_line2 =3D :ADDRESS_LINE2,
     address_line3 =3D :ADDRESS_LINE3,
     town =3D :TOWN,
     county =3D :COUNTY,
    WHERE id =3D :ID;
    COMMIT


With trying variations of the above, the problems seem to be:

1. I can't set the :ID value other than as it is already a parameter
2. With the "IF" statement included the query always fails with "Syntax
Error near IF"

Currently, I'm going to carry on working by building different sql statemen=
t
to insert or update based on wether my application knows that an INSERT or
UPDATE is needed, but I doubt this is the correct way of doing it.

One thing I really don't want to do is have to create stored procedures to
handle all inserts / updates etc as this seems to me to be alot of extra
overhead and won't actually improve the security of my application.

Any help would be very gratefully received.

Thanks.

Simon.

------=_Part_28826_14871890.1135869089518
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

<div>Hi All,&nbsp;</div>
<div>&nbsp;</div>
<div>Would appreciate&nbsp;some&nbsp;help&nbsp;with something that I think =
should&nbsp;be really easy but I've been tearing my hair out&nbsp;with all =
day.</div>
<div>&nbsp;</div>
<div>I want to execute a query&nbsp;which&nbsp;adds a new row into a table&=
nbsp;or updates an existing row if&nbsp;the id already&nbsp;exists.</div>
<div>&nbsp;</div>
<div>Something like the&nbsp;query I would like&nbsp;to execute is&nbsp;sho=
wn below...&nbsp; (I am trying to use&nbsp;&nbsp;Npgsql in C#&nbsp;incident=
ly).&nbsp;&nbsp;</div>
<div>&nbsp;</div>
<div>BEGIN<br>&nbsp;&nbsp;&nbsp;&nbsp;</div>
<div>IF :ID &lt; 0 THEN<br>&nbsp;&nbsp;&nbsp;&nbsp;INSERT INTO&nbsp;myTable=
 DEFAULT VALUES;<br>&nbsp;&nbsp;&nbsp;&nbsp;:ID =3D curval(properties);<br>=
&nbsp;&nbsp;&nbsp;&nbsp;</div>
<div>END&nbsp;</div>
<div><br>&nbsp;&nbsp;&nbsp;&nbsp;UPDATE&nbsp;myTable SET<br>&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;type =3D :TYPE,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;address_id =
=3D :ADDRESS_ID,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;address_line1 =3D :ADDRES=
S_LINE1,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;address_line2 =3D :ADDRESS_LINE2,=
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;address_line3 =3D :ADDRESS_LINE3,<br>&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;town =3D :TOWN,
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;county =3D :COUNTY,<br>&nbsp;&nbsp;&nbsp;=
&nbsp;WHERE id =3D :ID;</div>
<div>&nbsp;&nbsp;&nbsp;&nbsp;COMMIT</div>
<div>&nbsp;</div>
<div>&nbsp;</div>
<div>With trying variations of the above, the problems seem to be:</div>
<div>&nbsp;</div>
<div>1. I can't set the :ID value other than as it is already a parameter</=
div>
<div>2. With the &quot;IF&quot; statement included the query always fails w=
ith &quot;Syntax Error&nbsp;near IF&quot;&nbsp;</div>
<div>&nbsp;</div>
<div>Currently, I'm going to carry on working by building different sql sta=
tement to insert or update based on wether my application knows that an INS=
ERT or UPDATE is needed, but I doubt this is the correct way of doing it.
</div>
<div>&nbsp;</div>
<div>One thing I really don't want to do is have to create stored procedure=
s to handle all inserts / updates etc as this seems to me to be&nbsp;alot o=
f extra overhead&nbsp;and won't actually improve the security of my applica=
tion.
</div>
<div>&nbsp;</div>
<div>Any help would be very gratefully received.</div>
<div>&nbsp;</div>
<div>Thanks.</div>
<div>&nbsp;</div>
<div>Simon.</div>

------=_Part_28826_14871890.1135869089518--