Do not INSERT if UPDATE fails
Good evening,
I have a custom SQL function in PostgreSQL 9.5.7 which adds a "log entry"
to the table words_payments and then updates "vip_until" column in another
table:
CREATE OR REPLACE FUNCTION words_buy_vip(
in_sid text,
in_social integer,
in_tid text,
in_item text,
in_price float,
in_ip inet)
RETURNS integer AS
$func$
INSERT INTO words_payments (
sid,
social,
tid,
paid,
price,
ip
) VALUES (
in_sid,
in_social,
in_tid,
CURRENT_TIMESTAMP,
in_price,
in_ip
);
UPDATE words_users u
SET vip_until = CURRENT_TIMESTAMP + interval '1 year'
FROM words_social s
WHERE s.sid = in_sid
AND s.social = in_social
AND u.uid = s.uid
AND (u.vip_until IS NULL OR u.vip_until < CURRENT_TIMESTAMP)
RETURNING u.uid;
$func$ LANGUAGE sql;
However if the user record is not found or the user already has vip_until
= CURRENT_TIMESTAMP (i.e. the user has already purchased "vip status") I
would like to cancel the INSERT.
Is there please a way to rewrite the above function, without switching from
SQL to PL/pgSQL?
Regards
Alex
Does insert's "on conflict" clause not work for this usage?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Aug 2, 2017 at 8:58 AM, Alexander Farber <alexander.farber@gmail.com
wrote:
However if the user record is not found or the user already has vip_until
= CURRENT_TIMESTAMP (i.e. the user has already purchased "vip status") I
would like to cancel the INSERT.
You can "join" two DDL commands by using a Common Table Expression (CTE)
(i.e., WITH / SELECT). You would need to make it so the UPDATE happens
first and if there are no results the INSERT simply becomes a no-op.
David J.
On Wed, Aug 2, 2017 at 6:23 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
Does insert's "on conflict" clause not work for this usage?
Did you even bother to read the queries? He is using two different tables.
Francisco Olarte.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hello, I have followed David's suggestion (thank you!) -
On Wed, Aug 2, 2017 at 6:40 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:
On Wed, Aug 2, 2017 at 8:58 AM, Alexander Farber <
alexander.farber@gmail.com> wrote:However if the user record is not found or the user already has vip_until
= CURRENT_TIMESTAMP (i.e. the user has already purchased "vip status") I
would like to cancel the INSERT.
You can "join" two DDL commands by using a Common Table Expression (CTE)
(i.e., WITH / SELECT). You would need to make it so the UPDATE happens
first and if there are no results the INSERT simply becomes a no-op.
and the following works (if I change the function return type to VOID):
CREATE OR REPLACE FUNCTION words_buy_vip(
in_sid text,
in_social integer,
in_tid text,
in_item text,
in_price float,
in_ip inet)
RETURNS void AS
$func$
WITH cte AS (
UPDATE words_users u
SET vip_until = CURRENT_TIMESTAMP + interval '1 year'
FROM words_social s
WHERE s.sid = in_sid
AND s.social = in_social
AND u.uid = s.uid
AND (u.vip_until IS NULL OR u.vip_until <
CURRENT_TIMESTAMP)
RETURNING
u.uid AS uid,
in_sid AS sid,
in_social AS social,
in_tid AS tid,
in_price AS price,
in_ip AS ip
)
INSERT INTO words_payments (
sid,
social,
tid,
paid,
price,
ip
) SELECT
sid,
social,
tid,
CURRENT_TIMESTAMP,
price,
ip
FROM cte
-- RETURNING uid;
$func$ LANGUAGE sql;
But I wonder how to return the uid in the above statement?
(my original function returned integer uid)
Regards
Alex