Postgres Pro
Downloads
Home   >   mailing lists

Thread: Including SQL files

Including SQL files

From
Alexander Farber
Date:
Hello fellow pgsql users,

I am programming a word game backend in PL/pgSQL and have already reached a point, where (too) many stored functions are declared in a single file words.sql:

# SELECT proname || '(' || oidvectortypes(proargtypes) || ')'
        FROM pg_proc INNER JOIN pg_namespace ns ON (pg_proc.pronamespace = ns.oid)
        WHERE ns.nspname = 'public' ORDER BY proname;
                            ?column?
-----------------------------------------------------------------
 test1(integer)
 words_hash(character varying)
 words_join_new_game(integer, integer)
 words_merge_users(jsonb, inet)
 words_paying_user(integer)
 words_play(integer, integer, jsonb)
 words_shuffle(character varying[])
 words_skip_game(integer, integer)
 words_surrender_game(integer, integer)
 words_swap_game(integer, integer, character varying)
 words_trigger()
 words_valid_user(integer, character varying, character varying)
(12 rows)

I would prefer to have every stored function in a separate file (which would make reading git history easier too) and include them from words.sql.

Is there such a thing for PostgreSQL 9.5.1 available please and if not - could you share your approaches here?

The "EXEC SQL INCLUDE" described at http://www.postgresql.org/docs/9.5/static/ecpg-preproc.html seems to be something different?

Regards
Alex

Re: Including SQL files

From
Pavel Stehule
Date:
Hi

2016-03-21 15:42 GMT+01:00 Alexander Farber <alexander.farber@gmail.com>:
Hello fellow pgsql users,

I am programming a word game backend in PL/pgSQL and have already reached a point, where (too) many stored functions are declared in a single file words.sql:

# SELECT proname || '(' || oidvectortypes(proargtypes) || ')'
        FROM pg_proc INNER JOIN pg_namespace ns ON (pg_proc.pronamespace = ns.oid)
        WHERE ns.nspname = 'public' ORDER BY proname;
                            ?column?
-----------------------------------------------------------------
 test1(integer)
 words_hash(character varying)
 words_join_new_game(integer, integer)
 words_merge_users(jsonb, inet)
 words_paying_user(integer)
 words_play(integer, integer, jsonb)
 words_shuffle(character varying[])
 words_skip_game(integer, integer)
 words_surrender_game(integer, integer)
 words_swap_game(integer, integer, character varying)
 words_trigger()
 words_valid_user(integer, character varying, character varying)
(12 rows)

I would prefer to have every stored function in a separate file (which would make reading git history easier too) and include them from words.sql.

Is there such a thing for PostgreSQL 9.5.1 available please and if not - could you share your approaches here?

why you need it?

I developed large plpgsql application, and I used more than thirty files fit plpgsql functions. For deployment  I had prepared Makefile. One file holds one schema usually.

Regards

Pavel
 

The "EXEC SQL INCLUDE" described at http://www.postgresql.org/docs/9.5/static/ecpg-preproc.html seems to be something different?

Regards
Alex


Re: Including SQL files

From
Victor Yegorov
Date:
2016-03-21 16:42 GMT+02:00 Alexander Farber <alexander.farber@gmail.com>:
I would prefer to have every stored function in a separate file (which would make reading git history easier too) and include them from words.sql.

Is there such a thing for PostgreSQL 9.5.1 available please and if not - could you share your approaches here?

If you're processing your files with Postgres' `psql` tool, you can use `\i` directive to include other files.


--
Victor Y. Yegorov

Re: Including SQL files

From
Adrian Klaver
Date:
On 03/21/2016 07:42 AM, Alexander Farber wrote:
> Hello fellow pgsql users,
>
> I am programming a word game backend in PL/pgSQL and have already
> reached a point, where (too) many stored functions are declared in a
> single file words.sql:
>
> # SELECT proname || '(' || oidvectortypes(proargtypes) || ')'
>          FROM pg_proc INNER JOIN pg_namespace ns ON
> (pg_proc.pronamespace = ns.oid)
>          WHERE ns.nspname = 'public' ORDER BY proname;
>                              ?column?
> -----------------------------------------------------------------
>   test1(integer)
>   words_hash(character varying)
>   words_join_new_game(integer, integer)
>   words_merge_users(jsonb, inet)
>   words_paying_user(integer)
>   words_play(integer, integer, jsonb)
>   words_shuffle(character varying[])
>   words_skip_game(integer, integer)
>   words_surrender_game(integer, integer)
>   words_swap_game(integer, integer, character varying)
>   words_trigger()
>   words_valid_user(integer, character varying, character varying)
> (12 rows)
>
> I would prefer to have every stored function in a separate file (which
> would make reading git history easier too) and include them from words.sql.

So I am clear, what you are saying is that the definitions for the 12
functions you list above are all contained(external to the database) in
a text file words.sql. You would prefer that they be in separate files
externally with the ability to aggregate them in the words.sql by just
using an include statement instead of the complete text of each
function. Is this correct?

>
> Is there such a thing for PostgreSQL 9.5.1 available please and if not -
> could you share your approaches here?
>
> The "EXEC SQL INCLUDE" described at
> http://www.postgresql.org/docs/9.5/static/ecpg-preproc.html seems to be
> something different?
>
> Regards
> Alex
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Including SQL files

From
Alexander Farber
Date:
Thanks for your replies.

While I use "\i" regularly I just didn't realize it would be suitable here as well :-)

Regards
Alex


Re: Including SQL files

From
Igor Neyman
Date:

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alexander Farber
Sent: Monday, March 21, 2016 10:43 AM
To: pgsql-general <pgsql-general@postgresql.org>
Subject: [GENERAL] Including SQL files

 

Hello fellow pgsql users,

I am programming a word game backend in PL/pgSQL and have already reached a point, where (too) many stored functions are declared in a single file words.sql:

# SELECT proname || '(' || oidvectortypes(proargtypes) || ')'
        FROM pg_proc INNER JOIN pg_namespace ns ON (pg_proc.pronamespace = ns.oid)
        WHERE ns.nspname = 'public' ORDER BY proname;
                            ?column?
-----------------------------------------------------------------
 test1(integer)
 words_hash(character varying)
 words_join_new_game(integer, integer)
 words_merge_users(jsonb, inet)
 words_paying_user(integer)
 words_play(integer, integer, jsonb)
 words_shuffle(character varying[])
 words_skip_game(integer, integer)
 words_surrender_game(integer, integer)
 words_swap_game(integer, integer, character varying)
 words_trigger()
 words_valid_user(integer, character varying, character varying)
(12 rows)

I would prefer to have every stored function in a separate file (which would make reading git history easier too) and include them from words.sql.

Is there such a thing for PostgreSQL 9.5.1 available please and if not - could you share your approaches here?

The "EXEC SQL INCLUDE" described at http://www.postgresql.org/docs/9.5/static/ecpg-preproc.html seems to be something different?

Regards

Alex

 

 

Psql directive \i – is your friend.

In your words.sql:

\i words_hash

\i words_join_new_game

\i …

 

Regards,

Igor Neyman

Re: Including SQL files

From
mariusz
Date:
On Mon, 2016-03-21 at 15:58 +0100, Alexander Farber wrote:
> Thanks for your replies.
>
>
> While I use "\i" regularly I just didn't realize it would be suitable
> here as well :-)
>
\ir migth be a better option for a bundle of scripts, related to main
script (words.sql in your case), not to cwd

>
>
>