Thread: SQL equivalint of #incude directive ?

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

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.

Stan:

On Fri, Aug 30, 2019 at 3:49 PM stan <stanb@panix.com> 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.

Tony:

On Sun, Sep 1, 2019 at 10:05 AM Tony Shelver <tshelver@gmail.com> 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.

> From: stan <stanb@panix.com>
>
> 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
themain 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,
youare hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or
attachedto this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately
notifyus by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them
todisk. Thank you.