Re: Truncate if exists - Mailing list pgsql-hackers
From | Hannu Krosing |
---|---|
Subject | Re: Truncate if exists |
Date | |
Msg-id | 50788A61.2040603@krosing.net Whole thread Raw |
In response to | Re: Truncate if exists (Christopher Browne <cbbrowne@gmail.com>) |
List | pgsql-hackers |
On 10/12/2012 11:05 PM, Christopher Browne wrote: > On Fri, Oct 12, 2012 at 3:04 PM, Robert Haas <robertmhaas@gmail.com> wrote: >> On Thu, Oct 11, 2012 at 3:22 PM, Simon Riggs <simon@2ndquadrant.com> wrote: >>> So we just need a function called pg_if_table_exists(table, SQL) which >>> wraps a test in a subtransaction. >>> >>> And you write >>> >>> SELECT pg_if_table_exists('foo', 'TRUNCATE TABLE foo'); >>> >>> and we can even get rid of all that other DDL crud that's been added.... >>> >>> and we can have pg_if_table_not_exists() also. >> You could make this more composable by having pg_table_exists() and >> pg_execute_sql_from_string(). Then you can write: SELECT CASE WHEN >> pg_table_exists(...) THEN pg_execute_sql_from_string(...) END. And if >> you want the if-not-exists case then just stick a NOT in there. And >> if you want a more complicated condition, you can easily write that as >> well. > While that certainly has the merit of being compact, it mixes kinds of > evaluation (e.g. - parts of it are parsed at different times) and > requires quoting that isn't true for the other sorts of "IF EXISTS" > queries. > > To be sure, you can do anything you like inside a DO $$ $$ language > plpgsql; block, but it's not nice to have to do a lot of work > involving embedding code between languages. Makes it harder to > manipulate, analyze, and verify. > > Let me observe that Perl has, as one of its conditional concepts, the > notion of a "statement modifier" > <http://perldoc.perl.org/perlsyn.html#Statement-Modifiers>, which > corresponds pretty much to the IF EXISTS/IF NOT EXISTS modifiers that > have gotten added to Postgres over the last few versions. (I *think* > statement modifiers are attributable to SNOBOL, not 100% sure. I'm > pretty sure it predates Perl.) > > I suggest the though of embracing statement modifiers in DDL, with > some options possible: > a) { DDL STATEMENT } IF CONDITION; > b) { DDL STATEMENT } UNLESS CONDITION; We could even go as far as { DDL STATEMENT } IF CONDITION ELSE {ANOTHER DDL STATEMENT }; For example CREATE TABLE mytable(...) IF NOT EXISTS TABLE mytable ELSE TRUNCATE mytable; > > where CONDITION has several possible forms: > i) {IF|UNLESS} ( SQL expression returning T/F ) > ii) {IF|UNLESS} {EXISTS|NOT EXISTS} > {TABLE|SCHEMA|COLUMN|FUNCTION|...} object_name > > That feels like a cleaner extension than what we have had, with the IF > EXISTS/IF NOT EXISTS clauses that have been added to various > CREATE/DROP/ALTER commands.
pgsql-hackers by date: