Обсуждение: RE : How do I compile/test a PL/SQL in Postgresql
Hi,
I am a novice to PostgreSQL (although I know ORACLE’s PL/SQL very well)
I have written a Stored Function in PostgreSQL but cannot figure out how to compile it or run it in PostgreSQL. In ORACLE, one would have to
do this at SQL*PLUS prompt : @<file-path\file_name to compile the stored function into ORACLE DB.
In PostgreSQL, how do I do that?
In ORACLE, one would have to write a PL/SQL to test the stored function (and use DBMS_OUTPUT.PUT_LINE) to get the stored function to write to stdout.
In PostgreSQL, how do I test the stored function? I noticed none of the documentation or books seemed to mention this simple point.
Best regards
Hi you can do it at the command line: open a shell, change to your dbuser, then psql mydb or psql mydb -u USER -h HOST Or install pgAdminIII, a common GUI for postgres (I prefer the commandline.) If you use the commandline, make sure readline-support is installed. If you run (on the shell) your "CREATE OR REPLACE FUNCTION"-Statement, that's it. After this, you call your new function like this: select myfunc(arg); since functions are polymorphic, you have to use the appropriate amount of arguments, otherwise you will get the message that this function does not exist... yours, Christoph Patrick Ng schrieb: > Hi, > > > > I am a novice to PostgreSQL (although I know ORACLE's PL/SQL very well) > > > > I have written a Stored Function in PostgreSQL but cannot figure out how > to compile it or run it in PostgreSQL. In ORACLE, one would have to > > do this at SQL*PLUS prompt : @<file-path\file_name to compile the stored > function into ORACLE DB. > > In PostgreSQL, how do I do that? > > > > In ORACLE, one would have to write a PL/SQL to test the stored function > (and use DBMS_OUTPUT.PUT_LINE) to get the stored function to write to > stdout. > > > > In PostgreSQL, how do I test the stored function? I noticed none of the > documentation or books seemed to mention this simple point. > > > > Best regards > > > >
> I am a novice to PostgreSQL (although I know ORACLE's PL/SQL very well) > I have written a Stored Function in PostgreSQL but cannot figure out how > to compile it or run it in PostgreSQL. In ORACLE, one would have to I don't know if you've seen this link, but I should be useful. http://www.postgresql.org/docs/8.1/interactive/plpgsql-porting.html > do this at SQL*PLUS prompt : @<file-path\file_name to compile the stored > function into ORACLE DB. > In PostgreSQL, how do I do that? http://www.postgresql.org/files/documentation/books/aw_pgsql/node143.html#SECTION002411000000000000000 I would do: psql-> \i <file-path>\function.sql > In ORACLE, one would have to write a PL/SQL to test the stored function > (and use DBMS_OUTPUT.PUT_LINE) to get the stored function to write to > stdout. I believe that: psql-> \o filename or from the command line you can get query results returned to standard out. And then you could "pipe" the result to whatever you wanted. $ psql -u <user> -d <mydb> -c "select * from test" | grep -e "hello world" > hello.txt > In PostgreSQL, how do I test the stored function? I noticed none of the > documentation or books seemed to mention this simple point. Well, I would run it to see if it was syntactically correct. Then I would check to see if the results were as I expected. Next I would execute the function using "explain analyze" to see if there are any preformance issues that need to be resolved.
Hi Christoph, Thanks for the quick reply. I have placed my PL/SQL into a file. So at command line (after logging in using psql and getting the Postgresql prompt), how do I run the entire PL/SQL in the file? So I gather there is no compilation of PL/SQL under PostgreSQL (unlike ORACLE). One would just have to call the PL/SQL using SELECT statement and if it works, it means its OK? If it hits an error, how do I get error codes out? ORACLE PL/SQL can return error codes via SQLERRM and SQLCODE variables accessible within the PL/SQL? Does PostgreSQL also return the same error codes via accessible variables? Thank you & best regards -----Original Message----- From: Christoph Della Valle [mailto:christoph.dellavalle@goetheanum.ch] Sent: Monday, July 17, 2006 6:13 PM To: Patrick Ng Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] RE : How do I compile/test a PL/SQL in Postgresql Hi you can do it at the command line: open a shell, change to your dbuser, then psql mydb or psql mydb -u USER -h HOST Or install pgAdminIII, a common GUI for postgres (I prefer the commandline.) If you use the commandline, make sure readline-support is installed. If you run (on the shell) your "CREATE OR REPLACE FUNCTION"-Statement, that's it. After this, you call your new function like this: select myfunc(arg); since functions are polymorphic, you have to use the appropriate amount of arguments, otherwise you will get the message that this function does not exist... yours, Christoph Patrick Ng schrieb: > Hi, > > > > I am a novice to PostgreSQL (although I know ORACLE's PL/SQL very well) > > > > I have written a Stored Function in PostgreSQL but cannot figure out how > to compile it or run it in PostgreSQL. In ORACLE, one would have to > > do this at SQL*PLUS prompt : @<file-path\file_name to compile the stored > function into ORACLE DB. > > In PostgreSQL, how do I do that? > > > > In ORACLE, one would have to write a PL/SQL to test the stored function > (and use DBMS_OUTPUT.PUT_LINE) to get the stored function to write to > stdout. > > > > In PostgreSQL, how do I test the stored function? I noticed none of the > documentation or books seemed to mention this simple point. > > > > Best regards > > > > ________________________________________________________________________ This email has been scanned for all viruses by the MessageLabs Email Security System.
Hi Patrick at the command line you can use \i path\myfunction.sql (as mentioned by Richard Broersma Jr) if postgres compiles the function when you call "create function", I don't know, but I guess it does. if there are errors they show on the command line as well. Patrick Ng schrieb: > Hi Christoph, > > Thanks for the quick reply. I have placed my PL/SQL into a file. So at > command line (after logging in using psql and getting the Postgresql > prompt), how do I run the entire PL/SQL in the file? > > So I gather there is no compilation of PL/SQL under PostgreSQL (unlike > ORACLE). One would just have to call the PL/SQL using SELECT statement you call the "create function" statement once, then you use the select statement to call the new function. simple expl.: CREATE function func_test(integer) returns integer as $BODY$ select $1*2; $BODY$ LANGUAGE 'SQL'; SELECT func_test(34); SELECT func_test(12); > and if it works, it means its OK? If it hits an error, how do I get > error codes out? ORACLE PL/SQL can return error codes via SQLERRM and > SQLCODE variables accessible within the PL/SQL? Does PostgreSQL also > return the same error codes via accessible variables? > > Thank you & best regards > > -----Original Message----- > From: Christoph Della Valle [mailto:christoph.dellavalle@goetheanum.ch] > Sent: Monday, July 17, 2006 6:13 PM > To: Patrick Ng > Cc: pgsql-novice@postgresql.org > Subject: Re: [NOVICE] RE : How do I compile/test a PL/SQL in Postgresql > > Hi > > you can do it at the command line: > open a shell, change to your dbuser, then > psql mydb > or psql mydb -u USER -h HOST > > Or install pgAdminIII, a common GUI for postgres (I prefer the > commandline.) > If you use the commandline, make sure readline-support is installed. > > If you run (on the shell) your "CREATE OR REPLACE FUNCTION"-Statement, > that's it. After this, you call your new function like this: > > select myfunc(arg); > > since functions are polymorphic, you have to use the appropriate amount > of arguments, otherwise you will get the message that this function does > not exist... > > yours, > Christoph > > Patrick Ng schrieb: > >>Hi, >> >> >> >>I am a novice to PostgreSQL (although I know ORACLE's PL/SQL very > > well) > >> >> >>I have written a Stored Function in PostgreSQL but cannot figure out > > how > >>to compile it or run it in PostgreSQL. In ORACLE, one would have to >> >>do this at SQL*PLUS prompt : @<file-path\file_name to compile the > > stored > >>function into ORACLE DB. >> >>In PostgreSQL, how do I do that? >> >> >> >>In ORACLE, one would have to write a PL/SQL to test the stored > > function > >>(and use DBMS_OUTPUT.PUT_LINE) to get the stored function to write to >>stdout. >> >> >> >>In PostgreSQL, how do I test the stored function? I noticed none of > > the > >>documentation or books seemed to mention this simple point. >> >> >> >>Best regards >> >> >> >> > > > ________________________________________________________________________ > This email has been scanned for all viruses by the MessageLabs Email > Security System. > >
Hi, I login as : psql -U abc -d DB_NAME At the Postgresql prompt, I type \i d:\abc.sql but got a D:: Permission denied I have added MACHINE_NAME\postgres user to d: drive and its subfolders but am still getting the above error. Do you have any idea what can be wrong? abc.sql is a stored function. Best regards -----Original Message----- From: Richard Broersma Jr [mailto:rabroersma@yahoo.com] Sent: Monday, July 17, 2006 8:17 PM To: Patrick Ng; pgsql-novice@postgresql.org Subject: Re: [NOVICE] RE : How do I compile/test a PL/SQL in Postgresql > I am a novice to PostgreSQL (although I know ORACLE's PL/SQL very well) > I have written a Stored Function in PostgreSQL but cannot figure out how > to compile it or run it in PostgreSQL. In ORACLE, one would have to I don't know if you've seen this link, but I should be useful. http://www.postgresql.org/docs/8.1/interactive/plpgsql-porting.html > do this at SQL*PLUS prompt : @<file-path\file_name to compile the stored > function into ORACLE DB. > In PostgreSQL, how do I do that? http://www.postgresql.org/files/documentation/books/aw_pgsql/node143.htm l#SECTION002411000000000000000 I would do: psql-> \i <file-path>\function.sql > In ORACLE, one would have to write a PL/SQL to test the stored function > (and use DBMS_OUTPUT.PUT_LINE) to get the stored function to write to > stdout. I believe that: psql-> \o filename or from the command line you can get query results returned to standard out. And then you could "pipe" the result to whatever you wanted. $ psql -u <user> -d <mydb> -c "select * from test" | grep -e "hello world" > hello.txt > In PostgreSQL, how do I test the stored function? I noticed none of the > documentation or books seemed to mention this simple point. Well, I would run it to see if it was syntactically correct. Then I would check to see if the results were as I expected. Next I would execute the function using "explain analyze" to see if there are any preformance issues that need to be resolved. ________________________________________________________________________ This email has been scanned for all viruses by the MessageLabs Email Security System.
> psql -U abc -d DB_NAME > At the Postgresql prompt, I type \i d:\abc.sql > but got a D:: Permission denied > I have added MACHINE_NAME\postgres user to d: drive and its subfolders > but am still getting the above error. > Do you have any idea what can be wrong? abc.sql is a stored function. Here is another link for psql commands: http://www.postgresql.org/docs/8.0/static/app-psql.html if you notice your error message shows two colons in "D::". Maybe it is a path problem. try: psql> \!pwd --to get your current working directory. Also from the above page: Parsing for arguments stops when another unquoted backslash occurs. This is taken as the beginning of a new meta-command. The special sequence \\ (two backslashes) marks the end of arguments and continues parsing SQL commands, if any. That way SQL and psql commands can be freely mixed on a line. But in any case, the arguments of a meta-command cannot continue beyond the end of the line. So maybe the path "d:\abc.sql" the single \ is giving you problems. Maybe try d:\\abc.sql. or try: psql> \cd d:\\ --to change to this directory this simply try: psql> \i abc.sql regards, Richard Broersma Jr.
Sorry. Pse ignore. My mistake. The error was actually referring to something else. Invoking select metahsia.sf_pop_hsia_cal_year_tab(); Does work. Thanks alot -----Original Message----- From: Patrick Ng Sent: Thursday, July 20, 2006 11:15 PM To: 'Richard Broersma Jr'; pgsql-novice@postgresql.org Subject: RE: [NOVICE] RE : How do I compile/test a PL/SQL in Postgresql Hi Richard, I manage to compile a zero parameter stored function. However, when I invoke it : select metahsia.sf_pop_hsia_cal_year_tab(); I got this error : ERROR: function to_char(integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts It seems that its expecting no parameter to default to at least one integer parameter. Is this the case? Does this mean that there must always be at least one parameter for stored function. None of the documentation seems to explicit mention this. If I write a stored function (with zero parameters) and make it return void. It would theoretically behave like a stored procedure, it does a lot of batch processing on multiple tables and can expect no parameters (like main program in C and Java programs) Thank you & best regards -----Original Message----- From: Richard Broersma Jr [mailto:rabroersma@yahoo.com] Sent: Thursday, July 20, 2006 9:48 PM To: Patrick Ng; pgsql-novice@postgresql.org Subject: RE: [NOVICE] RE : How do I compile/test a PL/SQL in Postgresql > psql -U abc -d DB_NAME > At the Postgresql prompt, I type \i d:\abc.sql > but got a D:: Permission denied > I have added MACHINE_NAME\postgres user to d: drive and its subfolders > but am still getting the above error. > Do you have any idea what can be wrong? abc.sql is a stored function. Here is another link for psql commands: http://www.postgresql.org/docs/8.0/static/app-psql.html if you notice your error message shows two colons in "D::". Maybe it is a path problem. try: psql> \!pwd --to get your current working directory. Also from the above page: Parsing for arguments stops when another unquoted backslash occurs. This is taken as the beginning of a new meta-command. The special sequence \\ (two backslashes) marks the end of arguments and continues parsing SQL commands, if any. That way SQL and psql commands can be freely mixed on a line. But in any case, the arguments of a meta-command cannot continue beyond the end of the line. So maybe the path "d:\abc.sql" the single \ is giving you problems. Maybe try d:\\abc.sql. or try: psql> \cd d:\\ --to change to this directory this simply try: psql> \i abc.sql regards, Richard Broersma Jr. ________________________________________________________________________ This email has been scanned for all viruses by the MessageLabs Email Security System.
Hi Richard, I manage to compile a zero parameter stored function. However, when I invoke it : select metahsia.sf_pop_hsia_cal_year_tab(); I got this error : ERROR: function to_char(integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts It seems that its expecting no parameter to default to at least one integer parameter. Is this the case? Does this mean that there must always be at least one parameter for stored function. None of the documentation seems to explicit mention this. If I write a stored function (with zero parameters) and make it return void. It would theoretically behave like a stored procedure, it does a lot of batch processing on multiple tables and can expect no parameters (like main program in C and Java programs) Thank you & best regards -----Original Message----- From: Richard Broersma Jr [mailto:rabroersma@yahoo.com] Sent: Thursday, July 20, 2006 9:48 PM To: Patrick Ng; pgsql-novice@postgresql.org Subject: RE: [NOVICE] RE : How do I compile/test a PL/SQL in Postgresql > psql -U abc -d DB_NAME > At the Postgresql prompt, I type \i d:\abc.sql > but got a D:: Permission denied > I have added MACHINE_NAME\postgres user to d: drive and its subfolders > but am still getting the above error. > Do you have any idea what can be wrong? abc.sql is a stored function. Here is another link for psql commands: http://www.postgresql.org/docs/8.0/static/app-psql.html if you notice your error message shows two colons in "D::". Maybe it is a path problem. try: psql> \!pwd --to get your current working directory. Also from the above page: Parsing for arguments stops when another unquoted backslash occurs. This is taken as the beginning of a new meta-command. The special sequence \\ (two backslashes) marks the end of arguments and continues parsing SQL commands, if any. That way SQL and psql commands can be freely mixed on a line. But in any case, the arguments of a meta-command cannot continue beyond the end of the line. So maybe the path "d:\abc.sql" the single \ is giving you problems. Maybe try d:\\abc.sql. or try: psql> \cd d:\\ --to change to this directory this simply try: psql> \i abc.sql regards, Richard Broersma Jr. ________________________________________________________________________ This email has been scanned for all viruses by the MessageLabs Email Security System.
> select metahsia.sf_pop_hsia_cal_year_tab(); > I got this error : > ERROR: function to_char(integer) does not exist > HINT: No function matches the given name and argument types. You may > need to add explicit type casts > > It seems that its expecting no parameter to default to at least one > integer parameter. Is this the case? Does this mean that there must > always be at least one parameter for stored function. None of the > documentation seems to explicit mention this. Perhaps, to_char is not being used correctly. You could try: http://www.postgresql.org/docs/8.1/interactive/functions-formatting.html psql> \df -- to list all of avaliable functions to see if another one will work or you could try: http://www.postgresql.org/docs/8.1/interactive/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS CAST (integer 1234 AS char) But it is hard to say what the actual problem is with out seeing your function. Regards, Richard Broersma Jr.