Re: Conditional INSERT

You're right it probably does, unless the constraint needs to do a
sub-query to get the matching pattern, which would require a trigger.

On Fri, Mar 15, 2019 at 12:05 PM Rob Sargent <robjsargent(at)gmail(dot)com> wrote:

>
>
> On Mar 15, 2019, at 12:59 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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
> );
> On 15.03.19 19:17, Michael Lewis wrote:
>
> On Fri, Mar 15, 2019 at 10:55 AM basti <mailinglist(at)unix-solution(dot)de
> <mailto:mailinglist(at)unix-solution(dot)de <mailinglist(at)unix-solution(dot)de>>>
> wrote:
>
> Hello,
>
> I want to insert data into table only if condition is true.
> For example:
>
> INSERT into mytable (domainid, hostname, txtdata)
> VALUES (100,'_acme.challenge.example', 'somedata');
>
> Alternative to a trigger implementation, if you are generating that
> INSERT statement, you can change it to use a sub-select or CTE that
> contains no values if the domainid isn't what you like. If you want it
> to fail with error, you could add a check constraint. We might need more
> context on what you are doing and why to give good advice.
>
>
>
>
> Does a check constraint not suffice in this situation?
>
>