Thread: loading a funtion script from a file

Hi

 I've written a sql function in a text file, and now, i would like to upload into postgresql an execute, is there any command to do it? as far as I know in mysql exist source command, is there something similar in postgresql?

Thanks

--
Pau Marc Muñoz Torres

Laboratori de Biologia Computacional  
Institut de  Biotecnologia   i Biomedicina Vicent Villar                                    
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)
              
telèfon: 93 5812807
Email : paumarc.munoz@bioinf.uab.cat

Pau Marc Munoz Torres <paumarc@gmail.com> schrieb:

> Hi
>
>  I've written a sql function in a text file, and now, i would like to upload
> into postgresql an execute, is there any command to do it? as far as I know in
> mysql exist source command, is there something similar in postgresql?

Of course. Start psql and type:

\i /path/to/your/script.sql


Regards, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

APparently, from "man psql", -c can do only one thing at a time.  But you could do this with 2-3 commands (or 1 if you want to wrap the 2 up in a shell script or something).  Here's an example...

The text file that creates the script....

create or replace function trythis(varchar) returns varchar as $$

declare aname varchar(128);

begin

  select name into aname from templates limit 1;

  if not found then raise notice 'nuthin found'; end if;

  aname := aname||'---'||$1;

  return aname;

end;

$$ language plpgsql;

How to run it...

psql --dbname mydb -c "\i create_try.sql;"

psql --dbname mydb -c "select trythis('foo');"

psql --dbname mydb -c "drop function trythis(varchar);"

-dave

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andreas Kretschmer
Sent: Wednesday, November 21, 2007 10:50 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] loading a funtion script from a file

Pau Marc Munoz Torres <paumarc@gmail.com> schrieb:

> Hi

>

>  I've written a sql function in a text file, and now, i would like to upload

> into postgresql an execute, is there any command to do it? as far as I know in

> mysql exist source command, is there something similar in postgresql?

Of course. Start psql and type:

\i /path/to/your/script.sql

Regards, Andreas

--

Really, I'm not out to destroy Microsoft. That will just be a completely

unintentional side effect.                              (Linus Torvalds)

"If I was god, I would recompile penguin with --enable-fly."    (unknow)

Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

---------------------------(end of broadcast)---------------------------

TIP 5: don't forget to increase your free space map settings

Gauthier, Dave wrote:
> APparently, from "man psql", -c can do only one thing at a time.  But you could do this with 2-3 commands (or 1 if
youwant to wrap the 2 up in a shell script or something).  Here's an example... 
[snip]
> psql --dbname mydb -c "\i create_try.sql;"
> psql --dbname mydb -c "select trythis('foo');"
> psql --dbname mydb -c "drop function trythis(varchar);"

Or just put everything in one file and use -f <filename>
--
   Richard Huxton
   Archonet Ltd

On Nov 21, 2007 10:49 AM, Richard Huxton <dev@archonet.com> wrote:
> Gauthier, Dave wrote:
> > APparently, from "man psql", -c can do only one thing at a time.  But you could do this with 2-3 commands (or 1 if
youwant to wrap the 2 up in a shell script or something).  Here's an example... 
> [snip]
> > psql --dbname mydb -c "\i create_try.sql;"
> > psql --dbname mydb -c "select trythis('foo');"
> > psql --dbname mydb -c "drop function trythis(varchar);"
>
> Or just put everything in one file and use -f <filename>

And from the more than one way to skin a cat department:

cat my.sql | psql mydb
psql mydb < my.sql

Scott Marlowe wrote:
> On Nov 21, 2007 10:49 AM, Richard Huxton <dev@archonet.com> wrote:
>> Gauthier, Dave wrote:
>>> APparently, from "man psql", -c can do only one thing at a time.  But you could do this with 2-3 commands (or 1 if
youwant to wrap the 2 up in a shell script or something).  Here's an example... 
>> [snip]
>>> psql --dbname mydb -c "\i create_try.sql;"
>>> psql --dbname mydb -c "select trythis('foo');"
>>> psql --dbname mydb -c "drop function trythis(varchar);"
>> Or just put everything in one file and use -f <filename>
>
> And from the more than one way to skin a cat department:
>
> cat my.sql | psql mydb
> psql mydb < my.sql

Bearing in mind that although both mine and Scott's cats are skinless,
mine gave me line numbers in error messages.

--
   Richard Huxton
   Archonet Ltd

On Nov 21, 2007 11:16 AM, Richard Huxton <dev@archonet.com> wrote:
>
> Scott Marlowe wrote:
> > On Nov 21, 2007 10:49 AM, Richard Huxton <dev@archonet.com> wrote:
> >> Gauthier, Dave wrote:
> >>> APparently, from "man psql", -c can do only one thing at a time.  But you could do this with 2-3 commands (or 1
ifyou want to wrap the 2 up in a shell script or something).  Here's an example... 
> >> [snip]
> >>> psql --dbname mydb -c "\i create_try.sql;"
> >>> psql --dbname mydb -c "select trythis('foo');"
> >>> psql --dbname mydb -c "drop function trythis(varchar);"
> >> Or just put everything in one file and use -f <filename>
> >
> > And from the more than one way to skin a cat department:
> >
> > cat my.sql | psql mydb
> > psql mydb < my.sql
>
> Bearing in mind that although both mine and Scott's cats are skinless,
> mine gave me line numbers in error messages.

So do both of mine...  In fact, trying all four ways (\i, cat | psql,
psql < file.sql, and psql -f file) gave me the same error output.

Scott Marlowe wrote:
> On Nov 21, 2007 11:16 AM, Richard Huxton <dev@archonet.com> wrote:
>> Scott Marlowe wrote:
>>> On Nov 21, 2007 10:49 AM, Richard Huxton <dev@archonet.com> wrote:
>>>> Or just put everything in one file and use -f <filename>
>>> And from the more than one way to skin a cat department:
>>>
>>> cat my.sql | psql mydb
>>> psql mydb < my.sql
>> Bearing in mind that although both mine and Scott's cats are skinless,
>> mine gave me line numbers in error messages.
>
> So do both of mine...  In fact, trying all four ways (\i, cat | psql,
> psql < file.sql, and psql -f file) gave me the same error output.

Hmm - never used to... (checks)

Can't get the same here (v8.2) apart from the COPY errors.

=== begin test1.sql ===
BEGIN;

CREATE TABLE test1 (a int, b text, PRIMARY KEY (a)) ;

COPY test1 FROM STDIN;
1   AAA
2   BBB
3   CCC
1   AAA
\.

SELCT true;

ROLLBACK;
=== end test1.sql ===

$ psql82 -U richardh -f test1.sql
BEGIN
psql:test1.sql:3: NOTICE:  CREATE TABLE / PRIMARY KEY will create
implicit index "test1_pkey" for table "test1"
CREATE TABLE
psql:test1.sql:10: ERROR:  duplicate key violates unique constraint
"test1_pkey"
CONTEXT:  COPY test1, line 4: "1        AAA"
psql:test1.sql:12: ERROR:  syntax error at or near "SELCT"
LINE 1: SELCT true;
         ^
ROLLBACK

$ cat test1.sql | psql82 -U richardh
BEGIN
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"test1_pkey" for table "test1"
CREATE TABLE
ERROR:  duplicate key violates unique constraint "test1_pkey"
CONTEXT:  COPY test1, line 4: "1        AAA"
ERROR:  syntax error at or near "SELCT"
LINE 1: SELCT true;
         ^
ROLLBACK


--
   Richard Huxton
   Archonet Ltd

On Wed, Nov 21, 2007 at 11:10:15AM -0600, Scott Marlowe wrote:
> On Nov 21, 2007 10:49 AM, Richard Huxton <dev@archonet.com> wrote:
> > Gauthier, Dave wrote:
> > > APparently, from "man psql", -c can do only one thing at a time.  But you could do this with 2-3 commands (or 1
ifyou want to wrap the 2 up in a shell script or something).  Here's an example... 
> > [snip]
> > > psql --dbname mydb -c "\i create_try.sql;"
> > > psql --dbname mydb -c "select trythis('foo');"
> > > psql --dbname mydb -c "drop function trythis(varchar);"
> >
> > Or just put everything in one file and use -f <filename>
>
> And from the more than one way to skin a cat department:
>
> cat my.sql | psql mydb
> psql mydb < my.sql

Should anything go wrong with either of these constructs, you don't
get the line number where it did, so the following is better:

psql -1 -f my.sql

This ensures that the entire thing is run in one transaction, and when
anything goes wrong, you'll know the line number where it did.

Transactional DDL invaluable for changing schemas :)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Nov 21, 2007 12:21 PM, David Fetter <david@fetter.org> wrote:
>
> On Wed, Nov 21, 2007 at 11:10:15AM -0600, Scott Marlowe wrote:
> > On Nov 21, 2007 10:49 AM, Richard Huxton <dev@archonet.com> wrote:
> > > Gauthier, Dave wrote:
> > > > APparently, from "man psql", -c can do only one thing at a time.  But you could do this with 2-3 commands (or 1
ifyou want to wrap the 2 up in a shell script or something).  Here's an example... 
> > > [snip]
> > > > psql --dbname mydb -c "\i create_try.sql;"
> > > > psql --dbname mydb -c "select trythis('foo');"
> > > > psql --dbname mydb -c "drop function trythis(varchar);"
> > >
> > > Or just put everything in one file and use -f <filename>
> >
> > And from the more than one way to skin a cat department:
> >
> > cat my.sql | psql mydb
> > psql mydb < my.sql
>
> Should anything go wrong with either of these constructs, you don't
> get the line number where it did, so the following is better:

Umm, as I posted before, I DO get the line number.  the output I get
looks exactly the same as if I use -f.
Richard posted an example of when he did get the same thing, but not
one of where he didn't.

> psql -1 -f my.sql
>
> This ensures that the entire thing is run in one transaction, and when
> anything goes wrong, you'll know the line number where it did.
>
> Transactional DDL invaluable for changing schemas :)

That's handy, but I generally put the begin; commit; pair in my .sql
file anyway.  I'm a huge fan of transactional DDL.

Scott Marlowe wrote:
> On Nov 21, 2007 12:21 PM, David Fetter <david@fetter.org> wrote:
>> On Wed, Nov 21, 2007 at 11:10:15AM -0600, Scott Marlowe wrote:
>>> On Nov 21, 2007 10:49 AM, Richard Huxton <dev@archonet.com> wrote:
>>>> Gauthier, Dave wrote:
>>>>> APparently, from "man psql", -c can do only one thing at a time.  But you could do this with 2-3 commands (or 1
ifyou want to wrap the 2 up in a shell script or something).  Here's an example... 
>>>> [snip]
>>>>> psql --dbname mydb -c "\i create_try.sql;"
>>>>> psql --dbname mydb -c "select trythis('foo');"
>>>>> psql --dbname mydb -c "drop function trythis(varchar);"
>>>> Or just put everything in one file and use -f <filename>
>>> And from the more than one way to skin a cat department:
>>>
>>> cat my.sql | psql mydb
>>> psql mydb < my.sql
>> Should anything go wrong with either of these constructs, you don't
>> get the line number where it did, so the following is better:
>
> Umm, as I posted before, I DO get the line number.  the output I get
> looks exactly the same as if I use -f.
> Richard posted an example of when he did get the same thing, but not
> one of where he didn't.

(checks again). No, they're different:


$ psql82 -U richardh -f test1.sql
...
psql:test1.sql:12: ERROR:  syntax error at or near "SELCT"
$ cat test1.sql | psql82 -U richardh
...
ERROR:  syntax error at or near "SELCT"

The -f gives me line 12, from STDIN it doesn't.

>> psql -1 -f my.sql
>>
>> This ensures that the entire thing is run in one transaction, and when
>> anything goes wrong, you'll know the line number where it did.
>>
>> Transactional DDL invaluable for changing schemas :)
>
> That's handy, but I generally put the begin; commit; pair in my .sql
> file anyway.  I'm a huge fan of transactional DDL.

Hmm - didn't know the -1 thing. That's cool. I like to set ON_ERROR_STOP
too.

Almost as useful as BEGIN...COMMIT around schema changes is
BEGIN...ROLLBACK. I like to see it's all going to work before applying
the change. Of course, not always practical with changes to large tables.

--
   Richard Huxton
   Archonet Ltd

On Nov 21, 2007 1:07 PM, Richard Huxton <dev@archonet.com> wrote:
> Scott Marlowe wrote:
> > On Nov 21, 2007 12:21 PM, David Fetter <david@fetter.org> wrote:
> >> On Wed, Nov 21, 2007 at 11:10:15AM -0600, Scott Marlowe wrote:
> >>> On Nov 21, 2007 10:49 AM, Richard Huxton <dev@archonet.com> wrote:
> >>>> Gauthier, Dave wrote:
> >>>>> APparently, from "man psql", -c can do only one thing at a time.  But you could do this with 2-3 commands (or 1
ifyou want to wrap the 2 up in a shell script or something).  Here's an example... 
> >>>> [snip]
> >>>>> psql --dbname mydb -c "\i create_try.sql;"
> >>>>> psql --dbname mydb -c "select trythis('foo');"
> >>>>> psql --dbname mydb -c "drop function trythis(varchar);"
> >>>> Or just put everything in one file and use -f <filename>
> >>> And from the more than one way to skin a cat department:
> >>>
> >>> cat my.sql | psql mydb
> >>> psql mydb < my.sql
> >> Should anything go wrong with either of these constructs, you don't
> >> get the line number where it did, so the following is better:
> >
> > Umm, as I posted before, I DO get the line number.  the output I get
> > looks exactly the same as if I use -f.
> > Richard posted an example of when he did get the same thing, but not
> > one of where he didn't.
>
> (checks again). No, they're different:

SNIP

> The -f gives me line 12, from STDIN it doesn't.

Ahhh, now I see.  I assume that stdin acts the same as if you'd run
psql and typed the commands in one at a time, hence the LINE1: at the
beginning of that line.