Re: Syntax checking DO blocks and ALTER TABLE statements?

pá 19. 2. 2021 v 6:09 odesílatel Kevin Brannen <KBrannen(at)efji(dot)com> napsal:

> >From: Ron <ronljohnsonjr(at)gmail(dot)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

> 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.
>