On 10/12/2012 11:05 PM, Christopher Browne wrote:
On Fri, Oct 12, 2012 at 3:04 PM, Robert Haas <robertmh...@gmail.com> wrote:
On Thu, Oct 11, 2012 at 3:22 PM, Simon Riggs <si...@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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
