Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gStQ3-00032s-1e for pgsql-sql@arkaria.postgresql.org; Sat, 01 Dec 2018 00:46:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gStQ1-0000Zw-0c for pgsql-sql@arkaria.postgresql.org; Sat, 01 Dec 2018 00:46:01 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gStQ0-0000Zp-C7 for pgsql-sql@lists.postgresql.org; Sat, 01 Dec 2018 00:46:00 +0000 Received: from mx1.riseup.net ([198.252.153.129]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gStPg-0005RJ-GN for pgsql-sql@lists.postgresql.org; Sat, 01 Dec 2018 00:45:59 +0000 Received: from cotinga.riseup.net (cotinga-pn.riseup.net [10.0.1.164]) (using TLSv1 with cipher ECDHE-RSA-AES256-SHA (256/256 bits)) (Client CN "*.riseup.net", Issuer "COMODO RSA Domain Validation Secure Server CA" (verified OK)) by mx1.riseup.net (Postfix) with ESMTPS id 8D6D71A0423 for ; Fri, 30 Nov 2018 16:45:36 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=riseup.net; s=squak; t=1543625136; bh=k3GLJ27O7q1v+v0rB2GlHupM5tTaWv4CUU5yYHH9cGQ=; h=Subject:To:References:From:Date:In-Reply-To:From; b=rMZY31zjkR/ssCpu0waWBxgsNUbBRKFtucDLLInYIiri2UdqMEc3T7dW+b28MI4Dd 68dnHn55sxGxo0Jaqu1jS64xIb75wb/y1VgwJasiCTtUZDZM0AEaIbmhqhwiKTdJDc qknkSYoHJySBpQO3oVK7X7UMqfGGU/Fm4+ek0NJk= X-Riseup-User-ID: BCB054B4BC7B6882802533D92C0C4AF2EAE9AFFE97EB9FE6EFB8822793588796 Received: from [127.0.0.1] (localhost [127.0.0.1]) by cotinga.riseup.net with ESMTPSA id 347206EEE4 for ; Fri, 30 Nov 2018 16:45:35 -0800 (PST) Subject: Re: interval origami To: pgsql-sql@lists.postgresql.org References: <20181130175719.l7izv65wjwgkls35@alvherre.pgsql> <6d32b24f-2d3e-df79-81d2-3b1abb8cc4b4@riseup.net> <80ea3708-11be-e7e3-9f49-011a682e3bf4@riseup.net> From: Adam Jensen Openpgp: preference=signencrypt Autocrypt: addr=hanzer@riseup.net; keydata= xsFNBFITbtMBEADj0R/kXShGc0soC29c5WkR56N/nsQ/VgXExduZ3Agyu/5AintZDbcJ9pLO PsaYKJfchIa9Fv1lcLhaaGPyPBYTklU8axSDpDa2Y6Kc2Ezyjq6dDEdk3La2eWER8BDQHNP+ MYZ8Qin+tllQwX/uufC4UBvZxxIOhp05gAryvFPcXoq1MXrj5PdeNvFKtAVRNSwPDg1sIm6A ijZRcB6NfeMb/Yc543Yf450ewZTuebnJTQu55ywK5mpZExWiAZEr6z1cnbTfqG3Do82CWhak w16F5r0Vx25ckz0St8N5igdZOi57hS+dQLpxkVW2a6KqTQ+vbIjwEp4Kxj88PjI6CgZ9y6el mY4vmkDKXbGSDUjswUDQYuO2IXPT9GcE7H2grh2E5tvxH2DpL3lw5YFg5FPw6Ku8m1xYeh41 xZ+tLGoYs9Qg/PA2a7pDeWn8p227jJ9Q9MxAcA2KfXRA6qfMTc/gvYF3bsVYorV8XwNcoQWv WoYFz7gAhXvmkbad40c8fUr8cKP0aFnqlIvC0QBkhhRMocG/1PcahwIMepv+F2nNBOFeFmxr YvcYXzNWnG5S/QNJvc5jAvgNZMfYz7R7KJGLinjq7ED/5Ly/EaDFkDSwNAAxKkPabxfryhb3 6w7b509xa8EXRAi7reG04jFI+54PKMT5/FNrw9dhGnlJGU0KBQARAQABzR9BZGFtIEplbnNl biA8aGFuemVyQHJpc2V1cC5uZXQ+wsF3BBMBCgAhBQJSE27TAhsDBQsJCAcDBRUKCQgLBRYC AwEAAh4BAheAAAoJEGUApdpiqr3gNiAQAJ+lnXCQZZSsOYTxwXBlrVN5pepI8V+fd4ZZ3gn/ wt7dpP2Ak4FQNDch8XvahhS/guKvosZjSayqynPF2eXcR8sXNV2uMwKpqas139PM/956FqYv XdN/GOGVHxR9KanafzJwMJj5f6+BrhPp1GVRwQYyUhdlmnZJhHMt+lRLu1h27m7E/IAngYia RJ6a3m6XJQFkRMz8mjSNV0AZBlNNvpycEJoS8RbMoynR5LIjdWlJye25ufox1pjY/XVhw3hF 2HxRKAakO+q6Zc6v0MVmjDiIq+QxCqGdCoj0SACEmgOmI+SZl6rmenrSX0M/cktpNX9wyBYN CtxIdA6Zex+mOMmgo9g7T2n4xcqX83aIQKkuBJFfUj5yPBEWkjFvuYvtXbHogY3maFNYe+pm ZE6EZpBRZ8PCN17NLgxyh58yeJEx3oS1LDfqRz5SrFxfdN7bgExN3HKJJg7Jt32Oaml/8jSI BuxcEYimaP6MBkhrBHmd+TW3TUVGyQosqHGllQttXD92dm7wcaZnH0MNmroGG3+Czus2EgUS igFP4BwgNB8TaD17S6NQN83FVahAgjyhtadLtTzya/YSPrrvSHsKc88gasqNzvpsa+ggQZFX KALI/dozac61D6+UuTcUOvZCo5V4mIgeBMcw7PY6Ccycz9fHKghiPTuFdO1QI4KtujcXzsFN BFITbtMBEACnKEB3eI8FFIU4c9oDEkSPrZCwOOIjkaz9HfPOcLslFLVijqhPt4CCk5HM05KR yIYrYTyWytZfX+j2vW4TqJzntPBC+7oNjAIZ1l4UH6q8UxDya9+OLCx6awJNLo+5xmZ3pU7K vaTkvMguQfO7VWUK+f5nKYr/+lnZkxH0wuuLWomceEOk8Kt6g9+pQOC6w8FX4tYrtSFoSLHB bU7U1U4eJ3Hhp2b6PzHtJPtGORZ9i7CRRlZHONDNMw0EPcBkb5N0zgdLveVEUTfZfulR0TRN 2dBktBXArfoO3qZN4aKseLXU8XRQUG5xJNwf8vY+8fzKvgeh3BUgz2USYofRcTKEmkurvoA+ rKBMmGnW9s9pn8jlDLIB+V651YiJ+ip5HZL17pVI0zNDjKvqLw9EWGDaFh6WnIT+GTGKQBtu yzEZCKrPUkDxuqhZBRDw2ODdP37ccF9TippHW1e55oK3Wjgwon8DH7cJixzTXKIegTio459Y YE763IEXHjozhjZVYknaoysz4NRYJdYZKLjxsb4Xjc2BWLciumnEltaWGRl7MUblPH4jkMpD 4dOmYNpuhtUau8/UPpeHn8uaIHGlPGq4G+z5Wlaf6WVwLFOZRG8LuFMqhQ/8AeYjqDy3dB10 ruMDNpkiou/qDIIE2dHgvySMYntgPuEmCALHZMhIDvYCUwARAQABwsFfBBgBCgAJBQJSE27T AhsMAAoJEGUApdpiqr3gSxgP/jQJ34l85dNgrLqKOEOS7dfZzQRLjk0KkCIeCY/LmRDzyxD2 Gre5jtxFBuGNvcdaG5puC8rVpM0KW+MATqf8OjUhks71nz9aT+aKIga7oA/USl6QtErsdx7s g0BSz3kId481W3SUsvk7sXUcdRdri5SXZvfXhC9cL7UUKiPaL6zet/SMtIv0lSNGWHo44h3i IJVggPcwTW0p5eQGn8+dLTtJFBNfdpHv+pwgtBCBtMnYckfxkq+Mfk1vDhjVYQjtc03N6LbU zSALmELQeKdmjqhDZGniq651CveqMSaS6UKKbf2iIAMvBiyjl+jWGgUy9EMcNUJIxkAM8EWV Lv8MNu72oJhaAJpjSybiCeK2prK4ZTJF0h7ll81ftGVr1Cp/jRkw1Pof2bVQmlILHXkJLZJs unFsEcHpwnw52gZXc/zYjMGfyZy/ihP4Pyp+E7qlUKuLpkpEB43Ix5u8jmcOkMKhBnNo9OO1 p6Uwbl5GBIL/LXDKynYB98oXaxCzYkaxOrUWIu1ySJqRNnzAFMSwMqrR6fVumascjtAlBTMM sC/nN9VGG1VY0onGGJ80Vt+Fbhmj+zDpn0J3DfL2xD91+RxJaLCn8Iy/C4TikwsipXjnSmzA d6y+BJO5+lPxTTlg0UomOGv4ChmcVUEfXxk2GsvHZzsTA4qhLaZDuy9vR4eZ Message-ID: Date: Fri, 30 Nov 2018 19:45:35 -0500 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=utf-8 Content-Language: en-US Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Given the original: CREATE TABLE Example (start REAL, stop REAL, tag TEXT); I suppose it might make more sense to do it like this: CREATE FUNCTION remove_bad(gs REAL, gf REAL, bs REAL, bf REAL) RETURNS SETOF Example AS $$ BEGIN CASE WHEN ((bs <= gs) AND (bf < gf)) THEN RETURN QUERY VALUES (bf, gf, 'interesting'); WHEN ((bs > gs) AND (bf < gf)) THEN RETURN QUERY VALUES (gs, bs, 'interesting'), (bf, gf, 'interesting'); WHEN ((bs > gs) AND (bf >= gf)) THEN RETURN QUERY VALUES (gs, bs, 'interesting'); WHEN ((bs <= gs) AND (bf >= gf)) THEN RETURN; END CASE; END; $$ LANGUAGE plpgsql; SELECT remove_bad(10,40, 05,15); SELECT remove_bad(10,40, 10,15); SELECT remove_bad(10,40, 20,30); SELECT remove_bad(10,40, 20,40); SELECT remove_bad(10,40, 20,45); SELECT remove_bad(10,40, 05,40); SELECT remove_bad(10,40, 05,45); SELECT remove_bad(10,40, 10,40); SELECT remove_bad(10,40, 10,45);