From: Adrian Klaver <aklaver@comcast.net>
Reply-To: aklaver@comcast.net
To: pgsql-sql@postgresql.org
Subject: Re: Executing plpgsql scripts using psql, is that possible?
Date: Mon, 16 Jan 2006 18:35:48 -0800
User-Agent: KMail/1.6.2
Cc: Daniel CAUNE <d.caune@free.fr>
References: <0IT700IG8S07GF90@VL-MO-MR002.ip.videotron.ca>
In-Reply-To: <0IT700IG8S07GF90@VL-MO-MR002.ip.videotron.ca>
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Message-Id: <200601161835.48794.aklaver@comcast.net>

On Monday 16 January 2006 05:55 pm, Daniel CAUNE wrote:
> > -----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 possibl=
e?
> >
> > On Jan 16, 2006, at 5:35 AM, Daniel CAUNE wrote:
> > > 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:
> >
> > If you want to execute a plpgsql function from a file using psql,
> > just call it with SELECT. So your file might have:
> >
> > 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;
> >
> >
> > SELECT my_function(1);
> >
> >
> > 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=
=2E)
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
If you want to use plpgsql it will need to be within a function. In your re=
ply=20
you mention creating user-defined functions as part of the set up procedure=
=2E=20
It would not be weird to include the static data function as part of that=20
procedure and then call it to load the data. I see no reason to destroy the=
=20
function after use. If that is not the route you want to take you may want =
to=20
look at the following for information on using COPY to load data from a fil=
e=20
into a table-
www.postgresql.org/docs/8.1/interactive/sql-copy.html

=2D-=20
Adrian Klaver=09
aklaver@comcast.net