Re: Conditional INSERT

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@unix-solution.de
>>      <mailto:mailinglist@unix-solution.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.
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com