From: | Simon Gardner <slgard(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org(dot) |
Subject: | Problem with simple update query |
Date: | 2005-12-29 15:11:29 |
Message-ID: | cca13a160512290711t42b7d544u@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Ken Winter | 2005-12-29 17:46:28 | Arrays in PL/pgSQL routines? |
Previous Message | Daryl Richter | 2005-12-29 12:15:18 | Re: constraint and ordered value |