Re: [GENERAL] ISO something like "#if 0 ... #endif" for SQL code
In article <[EMAIL PROTECTED]>, Sam Mason <[EMAIL PROTECTED]> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ISO something like "#if 0 ... #endif" for SQL code
On Mon, Mar 10, 2008 at 12:28 PM, Craig Ringer <[EMAIL PROTECTED]> wrote: > > 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. I should get into that habit in any case. Thanks for pointing it out. Kynn
Re: [GENERAL] ISO something like "#if 0 ... #endif" for SQL code
On Tue, Mar 11, 2008 at 10:10 AM, Gurjeet Singh <[EMAIL PROTECTED]>
wrote:
> 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.
>
Ah! Finally I see what Martin was getting at in his reply.
Well, among those who seem unaware of the standard is the author of the
Emacs SQL mode, because its syntax highlighting gets all messed up with
nested C-style comments...
Thanks for the tip!
Kynn
P.S. For any interested Emacs user: as it happens, it was pretty easy to
fix sql.el to allow C-style comments to nest. It just required adding a
couple of n's, to change the lines
(modify-syntax-entry ?/ ". 14" table)
(modify-syntax-entry ?* ". 23" table)
to
(modify-syntax-entry ?/ ". 14n" table)
(modify-syntax-entry ?* ". 23n" table)
(actually either one of the two changes would have sufficed) in the syntax
table definition
(defvar sql-mode-syntax-table
(let ((table (make-syntax-table)))
;; C-style comments /**/ (see elisp manual "Syntax Flags"))
(modify-syntax-entry ?/ ". 14" table)
(modify-syntax-entry ?* ". 23" table)
;; double-dash starts comments
(modify-syntax-entry ?- ". 12b" table)
;; newline and formfeed end comments
(modify-syntax-entry ?\n "> b" table)
(modify-syntax-entry ?\f "> b" table)
;; single quotes (') delimit strings
(modify-syntax-entry ?' "\"" table)
;; double quotes (") don't delimit strings
(modify-syntax-entry ?\" "." table)
;; backslash is no escape character
(modify-syntax-entry ?\\ "." table)
table)
"Syntax table used in `sql-mode' and `sql-interactive-mode'.")
(I also had to restart Emacs to get this change to have an effect. Just
executing the revised definition was not enough. Maybe there's a way to do
achieve the same without restarting, but I couldn't think of it.)
Re: [GENERAL] ISO something like "#if 0 ... #endif" for SQL code
On Tue, Mar 11, 2008 at 7:17 AM, Sam Mason <[EMAIL PROTECTED]> 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. That's a neat trick. The display in the interactive *SQL* (for me anyway) is a bit buggy (some newlines are not being inserted where I would expect them), but this is only a minor annoyance. Being able to execute a chunk of buffer on the spot like that is just awesome. (I like Ctrl-C Ctrl-C OK, but I like Ctrl-C Ctrl-R even more.) Thanks! Kynn
Re: [GENERAL] ISO something like "#if 0 ... #endif" for SQL code
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ISO something like "#if 0 ... #endif" for SQL code
On Mon, Mar 10, 2008 at 9:20 PM, Kynn Jones <[EMAIL PROTECTED]> 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? > 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; -- [EMAIL PROTECTED] [EMAIL PROTECTED] 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
Re: [GENERAL] ISO something like "#if 0 ... #endif" for SQL code
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ISO something like "#if 0 ... #endif" for SQL code
/* * Inside SQL statement place Obligatory top post Comment here */ Bedankt Martin- - Original Message - Wrom: MKHJYFMYXOEAIJJPHSCRTNHGSWZIDREXCAXZOWCONEUQZAAFXISHJE To: "Kynn Jones" <[EMAIL PROTECTED]> Cc: "pgsql-general General" 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 > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ISO something like "#if 0 ... #endif" for SQL code
In article <[EMAIL PROTECTED]>, Alban Hertroys <[EMAIL PROTECTED]> 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). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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
Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
