Thread: ISO something like "#if 0 ... #endif" for SQL code

Kynn Jones wrote:
> Hi!  When it comes to programming SQL, my newbie approach is to write my
> code in a file test.sql, which I test from within psql by using
>
>   my_db=> \i /some/path/test.sql
>
> ...and (once I'm satisfied with the code) copy and paste it to a different
> file that has the SQL I've written so far for the project.
>
I'm not aware of any block comment facility like you desire, though
there could easily be one. It'd be handy, for sure. However, most good
text editors can do this for you anyway, so if yours can't maybe you
need to find a better programmer's editor (emacs, vim, Visual Studio's
editor, etc).

Personally I use vim to comment out small blocks. However, this is
rarely required as I break my SQL up into logical chunks in separate
files. If I need to concatenate a bunch of files I just produce a small
file for inclusion with psql's \i command or with the -f option that in
turn contains \i commands and often things like a wrapping BEGIN /
COMMIT block.

For example, there's a very long sequence of operations I perform while
testing some data migration SQL. Each step is in its own SQL file, but I
provide a wrapper to make it easier to run the whole lot. Sans
documentation and whitespace:

reimport.sql:
-------------
BEGIN;
\i truncate.sql
\i pre-fixups.sql
\i editions.sql
\i import_customers.sql
\i import_journal.sql
\i import_checks.sql
\i convert_bookings.sql
DELETE FROM import_info;
INSERT INTO import_info ( import_date, import_timestamp ) VALUES (
current_date, current_timestamp );
\i customer_merge.psql
COMMIT;
\i optimise.sql
----------------

Each SQL file is (relatively) small and self contained, but I can run
the whole process with a simple:

psql -f reimport.sql

or with \i reimport.sql . It's easy to do it step by step in psql, too,
examining the state after each step. If someone wants to run it without
using psql they can just concatenate the SQL files together in order for
the same effect.

If you organise your code well and break it up into sensible module it's
a pretty easy way to work, and not too different from how you probably
work in other languages.

One thing I find particularly important is to keep my function and
trigger definitions in a separate file to schema definitions. The
function/trigger file uses CREATE OR REPLACE FUNCTION and makes sure to
DROP TRIGGER ... IF EXISTS before using CREATE TRIGGER - so I can just
include it with \i in psql to load the latest function and trigger
definitions without having to muck about with the table structure,
dumping and reloading data, etc. I just:

$ psql -f schema/customer_functions.sql
DROP TRIGGER
DROP TRIGGER
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE TRIGGER
CREATE TRIGGER

... and any changes are applied.

--
Craig Ringer

On Mar 10, 2008, at 4:50 PM, Kynn Jones wrote:

> Hi!  When it comes to programming SQL, my newbie approach is to
> write my code in a file test.sql, which I test from within psql by
> using
>
>   my_db=> \i /some/path/test.sql
>
> ...and (once I'm satisfied with the code) copy and paste it to a
> different file that has the SQL I've written so far for the project.
>
> It's a rather cumbersome system, all the more so because I don't
> have a convenient way to comment out large chunks of code (which
> may include C-style comments).
>
> I'm thinking of something like the trick of surrounding C code with
> pairs of #if 0 and #endif, which effectively comments out code,
> even when it contains /* C-style comments */.
>
> Is there some similar trick for SQL code?

I have been playing with the idea of using cpp to pre-process such
files, and maybe even put them in a Makefile. I don't think there's
any reason that wouldn't be possible with SQL files. Added bonus, you
can use macros in your SQL, for things like environment paths (for
including other SQL files for example) etc.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,47d573f5233091666920879!

In article <DD18D9BB-C92D-4B4B-9829-54AC88152B0E@solfertje.student.utwente.nl>,
Alban Hertroys <dalroi@solfertje.student.utwente.nl> writes:

>> I'm thinking of something like the trick of surrounding C code with
>> pairs of #if 0 and #endif, which effectively comments out code,
>> even when it contains /* C-style comments */.
>>
>> Is there some similar trick for SQL code?

> I have been playing with the idea of using cpp to pre-process such
> files, and maybe even put them in a Makefile. I don't think there's
> any reason that wouldn't be possible with SQL files. Added bonus, you
> can use macros in your SQL, for things like environment paths (for
> including other SQL files for example) etc.

If it's OK to preprocess SQL, you could also use the M4 macro
processor which comes with every decent operating system (i.e. Unix).

/*
 * Inside SQL statement place Obligatory top post Comment here
 */
Bedankt
Martin-

----- Original Message -----
Wrom: MKHJYFMYXOEAIJJPHSCRTNHGSWZIDREXCAXZOWCONEUQZAAFXISHJE
To: "Kynn Jones" <kynnjo@gmail.com>
Cc: "pgsql-general General" <pgsql-general@postgresql.org>
Sent: Monday, March 10, 2008 12:55 PM
Subject: Re: [GENERAL] ISO something like "#if 0 ... #endif" for SQL code


> On Mar 10, 2008, at 4:50 PM, Kynn Jones wrote:
>
> > Hi!  When it comes to programming SQL, my newbie approach is to
> > write my code in a file test.sql, which I test from within psql by
> > using
> >
> >   my_db=> \i /some/path/test.sql
> >
> > ...and (once I'm satisfied with the code) copy and paste it to a
> > different file that has the SQL I've written so far for the project.
> >
> > It's a rather cumbersome system, all the more so because I don't
> > have a convenient way to comment out large chunks of code (which
> > may include C-style comments).
> >
> > I'm thinking of something like the trick of surrounding C code with
> > pairs of #if 0 and #endif, which effectively comments out code,
> > even when it contains /* C-style comments */.
> >
> > Is there some similar trick for SQL code?
>
> I have been playing with the idea of using cpp to pre-process such
> files, and maybe even put them in a Makefile. I don't think there's
> any reason that wouldn't be possible with SQL files. Added bonus, you
> can use macros in your SQL, for things like environment paths (for
> including other SQL files for example) etc.
>
> Alban Hertroys
>
> --
> If you can't see the forest for the trees,
> cut the trees and you'll see there is no forest.
>
>
> !DSPAM:737,47d573f5233091666920879!
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

On Mon, Mar 10, 2008 at 10:50:26AM -0500, Kynn Jones wrote:
> Hi!  When it comes to programming SQL, my newbie approach is to write my
> code in a file test.sql, which I test from within psql by using
>
>   my_db=> \i /some/path/test.sql
>
> ...and (once I'm satisfied with the code) copy and paste it to a different
> file that has the SQL I've written so far for the project.

I'm not quite sure if this would help your use case, but a few editors
allow you to send blocks of text to other processes.  For example, under
Emacs I can hit Ctrl+C twice and it will grab the current paragraph
and send it off to psql, showing the results in another window.  Once
I'm happy with the statement I leave it and move on to the next job
(committing changes to some SCM when appropriate).


  Sam

On Mon, Mar 10, 2008 at 9:20 PM, Kynn Jones <kynnjo@gmail.com> wrote:

If your sole objective is to comment out large chunks of SQL code, which in turn may have multi-line comments, then the simplest trick is to comment them using /* multi-line */ itself!

The SQL standard, and Postgres, allow you to nest comments; some commercial RDBMS' do not provide this, and hence people think it's not possible in SQL.

So following is possible, and you can \include this file in psql with your desired results:


/* temporary big comment to test only one query, and ignore others

/* my first query */
select 'hello world';

/* my second query */
select 200*201;

/* my application's query */
select ename from emp where empid = 10;

end big comment */

/* Currently testing this query, and do not want the above queries to be
 executed until I am finished with this. So, put everything above this
 comment in a huge multi-line comment */
select ename, dname from emp, dept where emp.deptid = dept.deptid;




--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad *
18° 32' 57.25"N, 73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco

http://gurjeet.frihost.net

Mail sent from my BlackLaptop device

Gurjeet Singh wrote:
> If your sole objective is to comment out large chunks of SQL code, which in
> turn may have multi-line comments, then the simplest trick is to comment
> them using /* multi-line */ itself!
>
> The SQL standard, and Postgres, allow you to nest comments; some commercial
> RDBMS' do not provide this, and hence people think it's not possible in SQL.

"People" included me too. Thanks for the pointer Gurjeet.

-
   Richard Huxton
   Archonet Ltd

In article <20080311111755.GR1653@frubble.xen.chris-lamb.co.uk>,
Sam Mason <sam@samason.me.uk> wrote:

>I'm not quite sure if this would help your use case, but a few editors
>allow you to send blocks of text to other processes.  For example, under
>Emacs I can hit Ctrl+C twice and it will grab the current paragraph
>and send it off to psql, showing the results in another window.  Once
>I'm happy with the statement I leave it and move on to the next job
>(committing changes to some SCM when appropriate).

You can do that with vi (or vim) as well. Sending a paragraph would
be !}psql