Re: Conditional INSERT - Mailing list pgsql-general
On Fri, Mar 15, 2019 at 11:59 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 3/15/19 11:54 AM, basti wrote:
> this is a dns database, and the client is update the _acme-challenge for
> LE certificates. I don't want that the client can insert "any" txt record.
> the client should only insert data if the hostname start with
> _acme-challenge. i have no control on client.
>
> i have try this rule but the server reject this with a endless loop:
To borrow a quote:
"I had a problem so I decided to use a rule, now I have two problems."
Do not use a rule. As suggested upstream use a BEFORE INSERT trigger,
you will be a lot happier.
>
> CREATE RULE insert_acme AS ON INSERT TO t_dnsadmin_records_txt
> WHERE NEW.hostname like '_acme-challenge%'
> DO INSERT INTO t_dnsadmin_records_txt VALUES (
> NEW.domainid,
> NEW.hostname,
> NEW.txtdata
> );
>
>
Just curious, but wanted to follow up on whether rules are across-the-board discouraged? I've seen disparaging comments about them, but I don't see any indication of that on the create rule page.
The other suggestion in this thread--a foreign key--will throw an error. Your suggestion of a before trigger might well be better (and if so, why?), but is there anything particularly wrong or bad about using a rule that would actually work? Something along these lines:
CREATE RULE insert_acme AS ON INSERT TO t_dnsadmin_records_txt
WHERE NOT NEW.hostname like '_acme-challenge%'
DO INSTEAD NOTHING;
WHERE NOT NEW.hostname like '_acme-challenge%'
DO INSTEAD NOTHING;
Thanks,
Ken
--
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
pgsql-general by date: