pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedExecuting plpgsql scripts using psql, is that possible?
6+ messages / 3 participants
[nested] [flat]
* Executing plpgsql scripts using psql, is that possible?
@ 2006-01-16 10:35 Daniel CAUNE <d.caune@free.fr>
2006-01-16 13:50 ` Re: Executing plpgsql scripts using psql, is that possible? John DeSoi <desoi@pgedit.com>
0 siblings, 1 reply; 6+ messages in thread
From: Daniel CAUNE @ 2006-01-16 10:35 UTC (permalink / raw)
To: pgsql-sql
Hi,
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:
Oracle sqlplus:
---------------
DECLARE
V_MyObjectID bigint;
BEGIN
V_MyObjectID := RegisterMyObject('a string', 'another string');
AddObjectProperty(V_MyObjectID, 'a string');
AddObjectProperty(V_MyObjectID, 'another string');
END;
SQL Server MSQuery:
-------------------
DECLARE @MyObjectID int
SET @MyObjectID = RegisterMyObject('a string', 'another string')
EXECUTE AddObjectProperty(MyObjectID, 'a string');
EXECUTE AddObjectProperty(MyObjectID, 'another string');
Any idea how I can translate such a script for psql? I mean, without creating a function that wraps the whole, of course! :-)
Thanks,
Daniel
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Executing plpgsql scripts using psql, is that possible?
2006-01-16 10:35 Executing plpgsql scripts using psql, is that possible? Daniel CAUNE <d.caune@free.fr>
@ 2006-01-16 13:50 ` John DeSoi <desoi@pgedit.com>
2006-01-17 01:55 ` Re: Executing plpgsql scripts using psql, is that possible? Daniel CAUNE <d.caune@free.fr>
0 siblings, 1 reply; 6+ messages in thread
From: John DeSoi @ 2006-01-16 13:50 UTC (permalink / raw)
To: Daniel CAUNE <d.caune@free.fr>; +Cc: pgsql-sql
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
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Executing plpgsql scripts using psql, is that possible?
2006-01-16 10:35 Executing plpgsql scripts using psql, is that possible? Daniel CAUNE <d.caune@free.fr>
2006-01-16 13:50 ` Re: Executing plpgsql scripts using psql, is that possible? John DeSoi <desoi@pgedit.com>
@ 2006-01-17 01:55 ` Daniel CAUNE <d.caune@free.fr>
2006-01-17 02:35 ` Re: Executing plpgsql scripts using psql, is that possible? Adrian Klaver <aklaver@comcast.net>
2006-01-17 02:54 ` Re: Executing plpgsql scripts using psql, is that possible? John DeSoi <desoi@pgedit.com>
0 siblings, 2 replies; 6+ messages in thread
From: Daniel CAUNE @ 2006-01-17 01:55 UTC (permalink / raw)
To: John DeSoi <desoi@pgedit.com>; +Cc: pgsql-sql
> -----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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Executing plpgsql scripts using psql, is that possible?
2006-01-16 10:35 Executing plpgsql scripts using psql, is that possible? Daniel CAUNE <d.caune@free.fr>
2006-01-16 13:50 ` Re: Executing plpgsql scripts using psql, is that possible? John DeSoi <desoi@pgedit.com>
2006-01-17 01:55 ` Re: Executing plpgsql scripts using psql, is that possible? Daniel CAUNE <d.caune@free.fr>
@ 2006-01-17 02:35 ` Adrian Klaver <aklaver@comcast.net>
2006-01-17 03:10 ` Re: Executing plpgsql scripts using psql, is that possible? Daniel CAUNE <d.caune@free.fr>
1 sibling, 1 reply; 6+ messages in thread
From: Adrian Klaver @ 2006-01-17 02:35 UTC (permalink / raw)
To: pgsql-sql; +Cc: Daniel CAUNE <d.caune@free.fr>
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é : 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
>
>
> ---------------------------(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 reply
you mention creating user-defined functions as part of the set up procedure.
It would not be weird to include the static data function as part of that
procedure and then call it to load the data. I see no reason to destroy the
function after use. If that is not the route you want to take you may want to
look at the following for information on using COPY to load data from a file
into a table-
www.postgresql.org/docs/8.1/interactive/sql-copy.html
--
Adrian Klaver
aklaver@comcast.net
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Executing plpgsql scripts using psql, is that possible?
2006-01-16 10:35 Executing plpgsql scripts using psql, is that possible? Daniel CAUNE <d.caune@free.fr>
2006-01-16 13:50 ` Re: Executing plpgsql scripts using psql, is that possible? John DeSoi <desoi@pgedit.com>
2006-01-17 01:55 ` Re: Executing plpgsql scripts using psql, is that possible? Daniel CAUNE <d.caune@free.fr>
2006-01-17 02:35 ` Re: Executing plpgsql scripts using psql, is that possible? Adrian Klaver <aklaver@comcast.net>
@ 2006-01-17 03:10 ` Daniel CAUNE <d.caune@free.fr>
0 siblings, 0 replies; 6+ messages in thread
From: Daniel CAUNE @ 2006-01-17 03:10 UTC (permalink / raw)
To: aklaver@comcast.net; pgsql-sql
> If you want to use plpgsql it will need to be within a function. In your
> reply
> you mention creating user-defined functions as part of the set up
> procedure.
> It would not be weird to include the static data function as part of that
> procedure and then call it to load the data. I see no reason to destroy
> the
> function after use. If that is not the route you want to take you may want
> to
> look at the following for information on using COPY to load data from a
> file
> into a table-
> www.postgresql.org/docs/8.1/interactive/sql-copy.html
>
Yes, COPY may be an interesting option too.
Thanks!
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Executing plpgsql scripts using psql, is that possible?
2006-01-16 10:35 Executing plpgsql scripts using psql, is that possible? Daniel CAUNE <d.caune@free.fr>
2006-01-16 13:50 ` Re: Executing plpgsql scripts using psql, is that possible? John DeSoi <desoi@pgedit.com>
2006-01-17 01:55 ` Re: Executing plpgsql scripts using psql, is that possible? Daniel CAUNE <d.caune@free.fr>
@ 2006-01-17 02:54 ` John DeSoi <desoi@pgedit.com>
1 sibling, 0 replies; 6+ messages in thread
From: John DeSoi @ 2006-01-17 02:54 UTC (permalink / raw)
To: Daniel CAUNE <d.caune@free.fr>; +Cc: pgsql-sql
Daniel,
On Jan 16, 2006, at 8:55 PM, Daniel CAUNE wrote:
> 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.
Sorry I misunderstood the question.
> 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?
\copy is the easiest way in psql to populate tables.
If you need more control, maybe copy the data to temp tables and then
write plpgsql as needed to insert the data into the final tables.
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-01-17 03:10 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-16 10:35 Executing plpgsql scripts using psql, is that possible? Daniel CAUNE <d.caune@free.fr>
2006-01-16 13:50 ` John DeSoi <desoi@pgedit.com>
2006-01-17 01:55 ` Daniel CAUNE <d.caune@free.fr>
2006-01-17 02:35 ` Adrian Klaver <aklaver@comcast.net>
2006-01-17 03:10 ` Daniel CAUNE <d.caune@free.fr>
2006-01-17 02:54 ` John DeSoi <desoi@pgedit.com>
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