Date: Mon, 16 Jan 2006 20:55:15 -0500
From: Daniel CAUNE <d.caune@free.fr>
Subject: Re: Executing plpgsql scripts using psql, is that possible?
In-reply-to: <BC6BBB56-E7E8-402D-9B06-2034C0132458@pgedit.com>
To: 'John DeSoi' <desoi@pgedit.com>
Cc: pgsql-sql@postgresql.org
Message-id: <0IT700IG8S07GF90@VL-MO-MR002.ip.videotron.ca>
MIME-version: 1.0
Content-type: text/plain; charset=utf-8
Content-transfer-encoding: quoted-printable
Thread-index: AcYao/1m7FW91fb7RKSaDj9geMUMOgAYX6Ow


> -----Message d'origine-----
> De : pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-
> owner@postgresql.org] De la part de John DeSoi
> Envoy=C3=A9 : lundi 16 janvier 2006 08:51
> =C3=80 : Daniel CAUNE
> Cc : pgsql-sql@postgresql.org
> Objet : Re: [SQL] Executing plpgsql scripts using psql, is that =
possible?
>=20
>=20
> On Jan 16, 2006, at 5:35 AM, Daniel CAUNE wrote:
>=20
> > I would like to write some administration plpgsql scripts that
> > populate some tables (dimension tables) and to execute them using
> > psql.  I=E2=80=99m not sure that is possible with psql as it is with =
Oracle
> > sqlplus or SQL Server MSQuery:
>=20
>=20
> If you want to execute a plpgsql function from a file using psql,
> just call it with SELECT. So your file might have:
>=20
> create or replace function my_function(params integer)
> returns integer as $$
> DECLARE
>    V_MyObjectID bigint;
> BEGIN
>    V_MyObjectID :=3D RegisterMyObject('a string', 'another string');
>    AddObjectProperty(V_MyObjectID, 'a string');
>    AddObjectProperty(V_MyObjectID, 'another string');
> ....
> END;
> $$ language plpgsql;
>=20
>=20
> SELECT my_function(1);
>=20
>=20
> and then psql -f script.sql my_db
>

Yes, but that requires creating a function while I would prefer not =
having do so, as I said in my previous mail: "I mean, without creating a =
function that wraps the whole, of course! :-)".  Why?  Actually this is =
not a function; this is a script that inserts static data into dimension =
tables such as Country, Language, etc.

I have several scripts responsible for creating the database and all the =
objects (tables, views, constraints, indexes, user-defined functions, =
etc.) of my project.  I would like to have some other scripts to =
initialize dimension tables, i.e. inserting static data in those tables. =
 The idea is to automate the whole creation and initialization of a =
database on a PostgreSQL server; I already have an Ant task that =
searches for SQL files, orders them, and runs them against the specified =
database server.  The database and all relative objects are set up in =
one step.

So, I completely understand that I can write an SQL script that:

  1 - creates a function that wraps SQL code that inserts static data =
into dimension tables.
  2 - executes that function
  3 - destroys that function

But actually that is a bit weird, isn't it?

Thanks,


Daniel