Re: 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

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL