Thread: Unable to create function via psql
Hello, I have a problem with creating function through script ran in psql. I'm running the script AdminPack.sql from the contrib package using this statement: sudo su postgres -c psql MyDB < /usr/share/postgresql/8.2/contrib/adminpack.sql Script performs without problem yet when I log into my software I get the error that pg_file_lenght does not exists. Opening the script in pgAdmin and hitting F5 creates the function and enables logging into the program without error. Can anyone help me with running the script correctly through shell. -- Łukasz Brodziak "What if everyting around You isn't quite as it seems, What if all the world You think You know is an inelaborate dream When You look at Your reflection is that all you want it to be What if You could look right through the cracks Would You find Yourself...... Find Yourself afraid to see"
Le 07/03/2011 09:17, Lukasz Brodziak a écrit : > Hello, > > I have a problem with creating function through script ran in psql. > I'm running the script AdminPack.sql from the contrib package using > this statement: > sudo su postgres -c psql MyDB < /usr/share/postgresql/8.2/contrib/adminpack.sql > > Script performs without problem yet when I log into my software I get > the error that pg_file_lenght does not exists. Opening the script in > pgAdmin and hitting F5 creates the function and enables logging into > the program without error. Can anyone help me with running the script > correctly through shell. You need to execute the script in the postgres database, not the MyDB database. -- Guillaume http://www.postgresql.fr http://dalibo.com
I still have the same error. Is there any other way of running the script outside pgAdmin? 2011/3/7 Guillaume Lelarge <guillaume@lelarge.info>: > Le 07/03/2011 09:17, Lukasz Brodziak a écrit : >> Hello, >> >> I have a problem with creating function through script ran in psql. >> I'm running the script AdminPack.sql from the contrib package using >> this statement: >> sudo su postgres -c psql MyDB < /usr/share/postgresql/8.2/contrib/adminpack.sql >> >> Script performs without problem yet when I log into my software I get >> the error that pg_file_lenght does not exists. Opening the script in >> pgAdmin and hitting F5 creates the function and enables logging into >> the program without error. Can anyone help me with running the script >> correctly through shell. > > You need to execute the script in the postgres database, not the MyDB > database. > > > -- > Guillaume > http://www.postgresql.fr > http://dalibo.com > -- Łukasz Brodziak "What if everyting around You isn't quite as it seems, What if all the world You think You know is an inelaborate dream When You look at Your reflection is that all you want it to be What if You could look right through the cracks Would You find Yourself...... Find Yourself afraid to see"
Le 07/03/2011 11:39, Lukasz Brodziak a écrit : > I still have the same error. Is there any other way of running the > script outside pgAdmin? > Can you give us the real error message you get? -- Guillaume http://www.postgresql.fr http://dalibo.com
Here it is: http://img69.imageshack.us/i/errorjn.png/ Again, when I perform AdminPack.sql in pgAdmin I don't get the error. 2011/3/7 Guillaume Lelarge <guillaume@lelarge.info>: > Le 07/03/2011 11:39, Lukasz Brodziak a écrit : >> I still have the same error. Is there any other way of running the >> script outside pgAdmin? >> > > Can you give us the real error message you get? > > > -- > Guillaume > http://www.postgresql.fr > http://dalibo.com > -- Łukasz Brodziak "What if everyting around You isn't quite as it seems, What if all the world You think You know is an inelaborate dream When You look at Your reflection is that all you want it to be What if You could look right through the cracks Would You find Yourself...... Find Yourself afraid to see"
Le 07/03/2011 11:54, Lukasz Brodziak a écrit : > Here it is: http://img69.imageshack.us/i/errorjn.png/ > > Again, when I perform AdminPack.sql in pgAdmin I don't get the error. > You don't do the same thing. If "performing" adminpack.sql is executing it, you also don't have an error with psql when you execute it. At least, the error message displayed has nothing to do with executing the adminpack SQL script. To get back to your error message, it simply says that the pg_file_length(text) function doesn't exist in the database your tool is connected too. So, first, you need to make sure that your tool connects to the database where you added the functions (IOW, where you executed the adminpack script). -- Guillaume http://www.postgresql.fr http://dalibo.com
Yes. The only DBs on the server are both templates, postgres and MyDB. Still don't why the problem with the function occurs only when the script is ran through psql and not through pgAdmin. 2011/3/7 Guillaume Lelarge <guillaume@lelarge.info>: > Le 07/03/2011 11:54, Lukasz Brodziak a écrit : >> Here it is: http://img69.imageshack.us/i/errorjn.png/ >> >> Again, when I perform AdminPack.sql in pgAdmin I don't get the error. >> > > You don't do the same thing. If "performing" adminpack.sql is executing > it, you also don't have an error with psql when you execute it. At > least, the error message displayed has nothing to do with executing the > adminpack SQL script. > > To get back to your error message, it simply says that the > pg_file_length(text) function doesn't exist in the database your tool is > connected too. So, first, you need to make sure that your tool connects > to the database where you added the functions (IOW, where you executed > the adminpack script). > > > -- > Guillaume > http://www.postgresql.fr > http://dalibo.com > -- Łukasz Brodziak "What if everyting around You isn't quite as it seems, What if all the world You think You know is an inelaborate dream When You look at Your reflection is that all you want it to be What if You could look right through the cracks Would You find Yourself...... Find Yourself afraid to see"
Lukasz Brodziak <lukasz.brodziak@gmail.com> wrote: > Still don't why the problem with the function occurs only when the > script is ran through psql and not through pgAdmin. It would help if you could provide a self-contained psql test script which demonstrates the problem. If the function is large, it would be good to pare it down to the smallest function which still shows the problem. -Kevin
Ok problem solved. I managed to get through this using remote connection from Windows machine using: psql -h 192.168.1.10 -U postgres -d MyDB < C:\adminpack.sql command Thank You all for You help and input on this subject. 2011/3/7 Kevin Grittner <Kevin.Grittner@wicourts.gov>: > Lukasz Brodziak <lukasz.brodziak@gmail.com> wrote: > >> Still don't why the problem with the function occurs only when the >> script is ran through psql and not through pgAdmin. > > It would help if you could provide a self-contained psql test script > which demonstrates the problem. If the function is large, it would > be good to pare it down to the smallest function which still shows > the problem. > > -Kevin > -- Łukasz Brodziak "What if everyting around You isn't quite as it seems, What if all the world You think You know is an inelaborate dream When You look at Your reflection is that all you want it to be What if You could look right through the cracks Would You find Yourself...... Find Yourself afraid to see"