From: Simon Gardner <slgard@gmail.com>
To: pgsql-sql@postgresql.org.
Subject: Problem with simple update query
Date: Thu, 29 Dec 2005 15:11:29 +0000
Message-ID: <cca13a160512290711t42b7d544u@mail.gmail.com> (raw)
Hi All,
Would appreciate some help with something that I think should be really easy
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 = curval(properties);
END
UPDATE myTable SET
type = :TYPE,
address_id = :ADDRESS_ID,
address_line1 = :ADDRESS_LINE1,
address_line2 = :ADDRESS_LINE2,
address_line3 = :ADDRESS_LINE3,
town = :TOWN,
county = :COUNTY,
WHERE id = :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 statement
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: slgard@gmail.com, pgsql-sql@postgresql.org.
Subject: Re: Problem with simple update query
In-Reply-To: <cca13a160512290711t42b7d544u@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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