Including SQL files

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

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

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks for your replies.

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

Regards
Alex

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general