Syntax checking DO blocks and ALTER TABLE statements?
How does one go about syntax checking this?
do $$
begin if exists (select 1 from information_schema.table_constraints
where constraint_name = 'error_to_web_service_error') then
raise notice 'EXISTS error_to_web_service_error';
else
ALTER TABLE web_service_error
ADD CONSTRAINT error_to_web_service_error FOREIGN KEY (error_id)
REFERENCES error_code(error_id)
ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE;
end if
end $$
(There are 222 ALTER TABLE ADD FOREIGN KEY statements that I'm wrapping in
similar DO blocks, and want to make sure the statements are clean.)
--
Angular momentum makes the world go 'round.
On Tue, Feb 16, 2021 at 3:43 PM Ron <ronljohnsonjr@gmail.com> wrote:
How does one go about syntax checking this?
(There are 222 ALTER TABLE ADD FOREIGN KEY statements that I'm wrapping in
similar DO blocks, and want to make sure the statements are clean.)
Begin a transaction, execute the DO, capture an error if there is one,
rollback the transaction.
David J.
David G. Johnston <david.g.johnston@gmail.com> writes:
On Tue, Feb 16, 2021 at 3:43 PM Ron <ronljohnsonjr@gmail.com> wrote:
How does one go about syntax checking this?
(There are 222 ALTER TABLE ADD FOREIGN KEY statements that I'm wrapping in
similar DO blocks, and want to make sure the statements are clean.)Begin a transaction, execute the DO, capture an error if there is one,
rollback the transaction.
As David points out, wrapping the whole thing in a transaction will at
least guarantee it all succeeds or it is all rollled back. This can be
frustrating if the statements are slow and there are a lot of them as it
can result in a very tedious do-run-fix cycle.
Something which can help is using an editor with good font highlighting
and parsing support. One interesting area I've not yet looked at is the
development of LSP (Language Server Protocol) servers for SQL. I've used
LSP for other languages with great success. The challenge with databases
is that there is enough variation between different vendor
implementations to make accurate parsing and validation tedious to
implement, so most solutions only focus on ANSI compliance. Still, that
can be very useful.
See https://github.com/lighttiger2505/sqls for one example of an LSP
server for SQL and https://microsoft.github.io/language-server-protocol/
for more background on LSP and what it can provide. Many editors,
including VSCode, VI, Emacs, TextMate etc now have some support for LSP.
--
Tim Cross
On Tue, Feb 16, 2021 at 4:28 PM Tim Cross <theophilusx@gmail.com> wrote:
David G. Johnston <david.g.johnston@gmail.com> writes:
On Tue, Feb 16, 2021 at 3:43 PM Ron <ronljohnsonjr@gmail.com> wrote:
How does one go about syntax checking this?
(There are 222 ALTER TABLE ADD FOREIGN KEY statements that I'm wrapping
in
similar DO blocks, and want to make sure the statements are clean.)
Begin a transaction, execute the DO, capture an error if there is one,
rollback the transaction.As David points out, wrapping the whole thing in a transaction will at
least guarantee it all succeeds or it is all rollled back. This can be
frustrating if the statements are slow and there are a lot of them as it
can result in a very tedious do-run-fix cycle.
I do presume that someone wanting to test their code in this manner would
be doing so in a test environment and an empty database. Which makes the
execution time very small.
I personally would also solve the "lot of them" problem by using dynamic
SQL, so one pretty much only has to test the code generator instead of all
the actual executions - which can simply be confirmed fairly quickly once
on a test database without the need for transactions.
David J.
David G. Johnston <david.g.johnston@gmail.com> writes:
On Tue, Feb 16, 2021 at 4:28 PM Tim Cross <theophilusx@gmail.com> wrote:
David G. Johnston <david.g.johnston@gmail.com> writes:
On Tue, Feb 16, 2021 at 3:43 PM Ron <ronljohnsonjr@gmail.com> wrote:
How does one go about syntax checking this?
(There are 222 ALTER TABLE ADD FOREIGN KEY statements that I'm wrapping
in
similar DO blocks, and want to make sure the statements are clean.)
Begin a transaction, execute the DO, capture an error if there is one,
rollback the transaction.As David points out, wrapping the whole thing in a transaction will at
least guarantee it all succeeds or it is all rollled back. This can be
frustrating if the statements are slow and there are a lot of them as it
can result in a very tedious do-run-fix cycle.I do presume that someone wanting to test their code in this manner would
be doing so in a test environment and an empty database. Which makes the
execution time very small.
True. However, it never ceases to amaze me how many places don't have
such environments. Far too often, my first task when commencing a new
engagement is to sort out environments and procedures to manage change.
I personally would also solve the "lot of them" problem by using dynamic
SQL, so one pretty much only has to test the code generator instead of all
the actual executions - which can simply be confirmed fairly quickly once
on a test database without the need for transactions.
Given the number, I think I would do the same. A good example of why
being 'lazy' can be a virtue. Faster and easier to write a procedure to
generate dynamic SQL than write out all those alter statements manually
or even write it using a scripting language and ODBC if there is
sufficient variation in the statements to make writing it in plsql
'messy'.
--
Tim Cross
On 2/16/21 5:44 PM, Tim Cross wrote:
Given the number, I think I would do the same. A good example of why
being 'lazy' can be a virtue. Faster and easier to write a procedure to
generate dynamic SQL than write out all those alter statements manually
or even write it using a scripting language and ODBC if there is
sufficient variation in the statements to make writing it in plsql
'messy'.
In my case, the statements are generated by Ora2Pg, and the DO blocks are
generated by a bash script I wrote. Input data can be messy, so want to
verify things before running.
Sure, vim is great at highlighting some problems, but certainly not all.
What I'm really looking for the PostgreSQL version of SQL Server's Ctrl-F5:
it runs just the parser and then stops,
--
Angular momentum makes the world go 'round.
Ron <ronljohnsonjr@gmail.com> writes:
On 2/16/21 5:44 PM, Tim Cross wrote:
Given the number, I think I would do the same. A good example of why
being 'lazy' can be a virtue. Faster and easier to write a procedure to
generate dynamic SQL than write out all those alter statements manually
or even write it using a scripting language and ODBC if there is
sufficient variation in the statements to make writing it in plsql
'messy'.In my case, the statements are generated by Ora2Pg, and the DO blocks are
generated by a bash script I wrote. Input data can be messy, so want to
verify things before running.Sure, vim is great at highlighting some problems, but certainly not all.
What I'm really looking for the PostgreSQL version of SQL Server's Ctrl-F5:
it runs just the parser and then stops,
Sounds like exactly what LSP aims to provide. Don't know if the LSP SQL
servers available are mature enough yet, but that is definitely the
objective. Real benefit is that it is editor agnostic. Once your editor
has LSP support, all you need to do is configure the server details and
you get parsing, completion, re-factoring, definition lookup etc.
--
Tim Cross
On 2/16/21 6:19 PM, Tim Cross wrote:
Ron <ronljohnsonjr@gmail.com> writes:
On 2/16/21 5:44 PM, Tim Cross wrote:
Given the number, I think I would do the same. A good example of why
being 'lazy' can be a virtue. Faster and easier to write a procedure to
generate dynamic SQL than write out all those alter statements manually
or even write it using a scripting language and ODBC if there is
sufficient variation in the statements to make writing it in plsql
'messy'.In my case, the statements are generated by Ora2Pg, and the DO blocks are
generated by a bash script I wrote. Input data can be messy, so want to
verify things before running.Sure, vim is great at highlighting some problems, but certainly not all.
What I'm really looking for the PostgreSQL version of SQL Server's Ctrl-F5:
it runs just the parser and then stops,Sounds like exactly what LSP aims to provide. Don't know if the LSP SQL
servers available are mature enough yet, but that is definitely the
objective. Real benefit is that it is editor agnostic. Once your editor
has LSP support, all you need to do is configure the server details and
you get parsing, completion, re-factoring, definition lookup etc.
Naively, it seems that it should be easy to add this to psql. The EXPLAIN
verb exists, so it shouldn't be hard to add SYNTAX, which does less work
than EXPLAIN.
--
Angular momentum makes the world go 'round.
From: Ron <ronljohnsonjr@gmail.com>
How does one go about syntax checking this?
do $$
begin if exists (select 1 from information_schema.table_constraints
where constraint_name = 'error_to_web_service_error') then
raise notice 'EXISTS error_to_web_service_error';
else
ALTER TABLE web_service_error
ADD CONSTRAINT error_to_web_service_error FOREIGN KEY (error_id)
REFERENCES error_code(error_id)
ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE;
end if
end $$(There are 222 ALTER TABLE ADD FOREIGN KEY statements that I'm wrapping in similar DO blocks, and want to make sure the statements are clean.)
I've always wondered why Pg doesn't have something like that built in, but I suppose the obvious answer is that no one has felt like scratching that itch.
Have you checked out: https://github.com/okbob/plpgsql_check
I don't know if it'll do everything you want, but maybe it'd help at least some. It's on my to-do list to check out one day when I have time. :)
HTH,
Kevin
This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.
pá 19. 2. 2021 v 6:09 odesílatel Kevin Brannen <KBrannen@efji.com> napsal:
From: Ron <ronljohnsonjr@gmail.com>
How does one go about syntax checking this?
do $$
begin if exists (select 1 from information_schema.table_constraints
where constraint_name = 'error_to_web_service_error') then
raise notice 'EXISTS error_to_web_service_error';
else
ALTER TABLE web_service_error
ADD CONSTRAINT error_to_web_service_error FOREIGN KEY(error_id)
REFERENCES error_code(error_id)
ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE;
end if
end $$(There are 222 ALTER TABLE ADD FOREIGN KEY statements that I'm wrapping
in similar DO blocks, and want to make sure the statements are clean.)
I've always wondered why Pg doesn't have something like that built in, but
I suppose the obvious answer is that no one has felt like scratching that
itch.
plpgsql_check is my cleaned previous project plpgsql_lint. Main target of
this work was integration to upstream. Unfortunately there is not an
agreement on how this feature should be implemented - there is a very
fundamental difference in opinions, so this patch was rejected (I spent a
lot of time working on this patch). On second hand - with an outer
development I had more space for faster more experimental development and I
can quickly push new features to all supported pg releases (not just to
fresh release). This is an advantage of extensions - the development can be
much faster and because plpgsql_check is well isolated (it depends mostly
only on plpgsql runtime), then the development and maintenance is not too
difficult and expensive. There is only one harder task (for me) - making
builds for MS Win.
Now plpgsql_check is a relatively bigger project - so it is hard to merge
it to upstream, but it can live well on github simillary like PostGIS.
Regards
Pavel
Show quoted text
Have you checked out: https://github.com/okbob/plpgsql_check
I don't know if it'll do everything you want, but maybe it'd help at least
some. It's on my to-do list to check out one day when I have time. :)HTH,
Kevin
This e-mail transmission, and any documents, files or previous e-mail
messages attached to it, may contain confidential information. If you are
not the intended recipient, or a person responsible for delivering it to
the intended recipient, you are hereby notified that any disclosure,
distribution, review, copy or use of any of the information contained in or
attached to this message is STRICTLY PROHIBITED. If you have received this
transmission in error, please immediately notify us by reply e-mail, and
destroy the original transmission and its attachments without reading them
or saving them to disk. Thank you.