SQL equivalint of #incude directive ?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL equivalint of #incude directive ?

stan-9

I thought this would be common. But a quick Google only revealed what look to be
workarounds.

I am defining a bunch of functions, and I would prefer to store them in a
separate file, which then gets "source" by the main DB init file.

Is there a standard way to do this?

--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                                                -- Benjamin Franklin



Reply | Threaded
Open this post in threaded view
|

Re: SQL equivalint of #incude directive ?

Pavel Stehule


pá 30. 8. 2019 v 15:49 odesílatel stan <[hidden email]> napsal:

I thought this would be common. But a quick Google only revealed what look to be
workarounds.

I am defining a bunch  of functions, and I would prefer to store them in a
separate file, which then gets "source" by the main DB init file.

Is there a standard way to do this?

no, it isn't

Pavel


--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                                                -- Benjamin Franklin



Reply | Threaded
Open this post in threaded view
|

Re: SQL equivalint of #incude directive ?

Thomas Kellerer
In reply to this post by stan-9
stan schrieb am 30.08.2019 um 15:48:
> I thought this would be common. But a quick Google only revealed what look to be
> workarounds.
>
> I am defining a bunch of functions, and I would prefer to store them in a
> separate file, which then gets "source" by the main DB init file.
>
> Is there a standard way to do this?


Define "standard".

With psql you can use the \i directive from within a SQL script.

But that does not work with other SQL clients.

It all depends on the SQL client you use to run those scripts.

I use Liquibase to manage schema setup and migration - that has a totally different "include" directive then psql.





Reply | Threaded
Open this post in threaded view
|

Re: SQL equivalint of #incude directive ?

Francisco Olarte
In reply to this post by stan-9
Stan:

On Fri, Aug 30, 2019 at 3:49 PM stan <[hidden email]> wrote:
...
> I am defining a bunch  of functions, and I would prefer to store them in a
> separate file, which then gets "source" by the main DB init file.
> Is there a standard way to do this?

I assume you mean "standard for postgres", AFAIK there is not even a
(multidb) standard way to feed an sql script to a server (content of
queries / results are standard, how to send them / get them from the
server is not ).

\i in psql has been pointed. I do not know if this is one of your
"workarounds", but what I normally do for this is trat the "init"
script as a program and "make" it. I've done it using many of the
preprocessor freely available around ( i.e. m4, cpp ) and also using a
perl program ( but this is becuase I'm quite fluent in it and it makes
some thing easier ), and even doing "cat *.sql | psql" and naming the
scripts appropiately. You'll probably have it easier going by that
route ( generating the sql script from chunks using a tool of your
choice, it's just a big text chunk and you can easily debug it with a
text editor ), and it is not that complex.

Francisco Olarte.


Reply | Threaded
Open this post in threaded view
|

Re: SQL equivalint of #incude directive ?

Tony Shelver
Similar to what Francisco said.  Not exactly sure what your use case is though..

In the past I have usually used a DB modeling / design front end tool to design my database, and then maintain and generate most of the build scripts.
Datanamic Dezign used to be my go-to for SQL Server when i still paid money for tools, and I maintained that as a central repository for all SQL scripts for functions, tables, views, procedures and so on, and it would generate create / diff scripts as necessary.

On Postgres I use pgmodeler, the only issue there is that I find it quicker and easier to create procedures and functions and so on directly in code, then have those in a script file that is run after the schema creation.
There are other (commercial) tools out there that will allow SQL script preprocessing and generation.

I think you will find that most DBAs build their own scripts using tools like Perl or Python, or a commercial product.

A similar situation exists for HTML, there is no standard pre-processor directive.  I started off creating a Python utility to replace include directives with input from external files, which worked fine.  Fairly trivial if you are reasonably familiar with a scripting language.
Eventually i switched to an open source site generator that included that capability along with a lot more functionality.



On Sat, 31 Aug 2019 at 10:12, Francisco Olarte <[hidden email]> wrote:
Stan:

On Fri, Aug 30, 2019 at 3:49 PM stan <[hidden email]> wrote:
...
> I am defining a bunch  of functions, and I would prefer to store them in a
> separate file, which then gets "source" by the main DB init file.
> Is there a standard way to do this?

I assume you mean "standard for postgres", AFAIK there is not even a
(multidb) standard way to feed an sql script to a server (content of
queries / results are standard, how to send them / get them from the
server is not ).

\i in psql has been pointed. I do not know if this is one of your
"workarounds", but what I normally do for this is trat the "init"
script as a program and "make" it. I've done it using many of the
preprocessor freely available around ( i.e. m4, cpp ) and also using a
perl program ( but this is becuase I'm quite fluent in it and it makes
some thing easier ), and even doing "cat *.sql | psql" and naming the
scripts appropiately. You'll probably have it easier going by that
route ( generating the sql script from chunks using a tool of your
choice, it's just a big text chunk and you can easily debug it with a
text editor ), and it is not that complex.

Francisco Olarte.


Reply | Threaded
Open this post in threaded view
|

Re: SQL equivalint of #incude directive ?

Francisco Olarte
Tony:

On Sun, Sep 1, 2019 at 10:05 AM Tony Shelver <[hidden email]> wrote:
> I think you will find that most DBAs build their own scripts using tools like Perl or Python, or a commercial product.

Tools like Perl are great for building text output, it's got a lot of
shortcuts for a lot of things. Not that sure about python. But I think
this are more used by developers wearing dba hats, or for dbas which
double duty as developer / sysadmins. Any dba needs some programming
skills, specially when dealing with dbs with procedure capabilities,
but not that much.

For a pure DBA I would recommend more things like a classic
preprocessor (I've got some success with m4, specially when using
m4sugar with it, and I'm sure there are better ones around there) or
some templating system. For just text CPP can be used, but it knows a
bit about C syntax which can become a nuisance, but for just includes
it can be fine.

> A similar situation exists for HTML, there is no standard pre-processor directive.

IMO the reason is the same. SQL and HTML are not "source code", they
are the final product, the thing you send over an http connection ( in
html case ) or an implementation defined way ( i.e. inside the socket
connections psql makes using libpq ). They are like the window exes,
or the unix elf binaries. In the HTML case there are lots of
templating systems which you use to build the HTML content, in many
cases on the fly, and many of them can be reused easily for SQL.

I think the trick is not treating the SQL as the source, but as an EXE
file. Once you start thinking of it as such lots of things in your
usual toolbox become useful. I routinely do something similar for
configuration files, using just shell scripts full of echo+multiline
constant + source aux files + source some echoing files and, if quotes
are used for something like column names, cat + here docs.

Francisco Olarte.


Reply | Threaded
Open this post in threaded view
|

RE: SQL equivalint of #incude directive ?

Kevin Brannen
In reply to this post by stan-9
> From: stan <[hidden email]>
>
> I thought this would be common. But a quick Google only revealed what look to be workarounds.
>
> I am defining a bunch of functions, and I would prefer to store them in a separate file, which then gets "source" by the main DB init file.
>
> Is there a standard way to do this?

Besides what the others have said, not if you stick to SQL. This is really
more of an application level question where you have something that
will do your init/load process.

If you're willing to use other tools, then there are solutions. IIRC, your
other thread had "#include" in the title. You could build a small file with:

#include "function1.sql"
#include "function2.sql"
...

Then run it thru "cpp" and use the output of that. Might need some
post-processing if you're unlucky; I'd have to try it to see if there are
extraneous lines.

Personally, I'd probably just have a shell script that goes into that dir
and does something like:

cat *.sql | psql -d DB # and other args

and call it done...translate if you're not on a Unix-like system.

I could write a Perl program to do it because Perl can do almost anything. ;)

Your imagination is the limit.

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.