From: Daniel CAUNE <d.caune@free.fr>
To: John DeSoi <desoi@pgedit.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Executing plpgsql scripts using psql, is that possible?
Date: Mon, 16 Jan 2006 20:55:15 -0500
Message-ID: <0IT700IG8S07GF90@VL-MO-MR002.ip.videotron.ca> (raw)
In-Reply-To: <BC6BBB56-E7E8-402D-9B06-2034C0132458@pgedit.com>
> -----Message d'origine-----
> De : pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-
> owner@postgresql.org] De la part de John DeSoi
> Envoyé : lundi 16 janvier 2006 08:51
> À : Daniel CAUNE
> Cc : pgsql-sql@postgresql.org
> Objet : Re: [SQL] Executing plpgsql scripts using psql, is that possible?
>
>
> 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’m 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 := 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.) 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: d.caune@free.fr, desoi@pgedit.com
Subject: Re: Executing plpgsql scripts using psql, is that possible?
In-Reply-To: <0IT700IG8S07GF90@VL-MO-MR002.ip.videotron.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox