From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Do not INSERT if UPDATE fails |
Date: | 2017-08-02 15:58:45 |
Message-ID: | CAADeyWjpRHqP3ySQK1gctu2DpUYvRVXYA9Bvdg-WES8QO4wd9g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Edmundo Robles | 2017-08-02 16:02:55 | Would you add a --dry-run to pg_restore? |
Previous Message | Chris Travers | 2017-08-02 15:55:42 | Re: bidirectional mapping? |