Обсуждение: plpgsql - can't get a simple block to execute
I'm resending as I didn't get any response earlier. > This should be simple, but it's not. I think I'm just missing something > simple. I converted an oracle PL/SQL program to plpgssql, but it wouldn't > work, so I tried some very simple blocks, and even they fail. As far as I > can tell, the syntax is all valid. > Any help would be appreciated. > (postgres) robux:/opt/postgres/VRNJED > ->cat it.sql > > \echo raise notice attempt > begin > raise notice 'hello'; > end; > > \echo select into attempt > declare > i_int integer; > begin > select count(1) into i_int from vrnsys_version; > end; > > (postgres) robux:/opt/postgres/VRNJED > ->psql -U vrnsys > Welcome to psql 7.3.2, the PostgreSQL interactive terminal. > > Type: \copyright for distribution terms > \h for help with SQL commands > \? for help on internal slash commands > \g or terminate with semicolon to execute query > \q to quit > > VRNJED=> \i it.sql > raise notice attempt > psql:it.sql:4: ERROR: parser: parse error at or near "raise" at character > 9 > psql:it.sql:5: WARNING: COMMIT: no transaction in progress > COMMIT > select into attempt > psql:it.sql:9: ERROR: parser: parse error at or near "integer" at > character 17 > psql:it.sql:11: ERROR: parser: parse error at or near "select" at > character 8 > psql:it.sql:12: WARNING: COMMIT: no transaction in progress > COMMIT > VRNJED-> \q > (postgres) robux:/opt/postgres/VRNJED > -> > > Jed S. Walker > > >
"Walker, Jed S" <Jed_Walker@cable.comcast.com> writes:
>> This should be simple, but it's not. I think I'm just missing something
>> simple. I converted an oracle PL/SQL program to plpgssql, but it wouldn't
>> work, so I tried some very simple blocks, and even they fail. As far as I
>> can tell, the syntax is all valid.
You can't just type plpgsql statements at the SQL interpreter ... it's a
different language. You have to wrap those statements within a function
definition. (This is unlike Oracle, I believe, which does think it's
all one language. PG keeps a strong separation because it supports more
than one function programming language.)
Also, don't forget you have to install plpgsql support into each
database you want to use it in; it's not enabled by default, due to
probably-overly-paranoid security concerns. See the "createlang" shell
script for the easy way to do this.
regards, tom lane
On Tue, Apr 05, 2005 at 12:53:30PM -0600, Walker, Jed S wrote: > > I'm resending as I didn't get any response earlier. Hmmm...did you not see my response on Friday? http://archives.postgresql.org/pgsql-novice/2005-04/msg00016.php -- Michael Fuhr http://www.fuhr.org/~mfuhr/
No I didn't see that response. Thanks for resending. I am on 7.3 because the admins installed it (said it was the only version that could find RPMs for, up til now I've not been concerned). The production box is going to be 8.0.1 so when that is ready maybe I'll be able to do this. I'll wait until I get 8.0.1 and see what happens. -----Original Message----- From: Michael Fuhr [mailto:mike@fuhr.org] Sent: Tuesday, April 05, 2005 2:22 PM To: Walker, Jed S Cc: 'pgsql-novice@postgresql.org' Subject: Re: [NOVICE] plpgsql - can't get a simple block to execute On Tue, Apr 05, 2005 at 12:53:30PM -0600, Walker, Jed S wrote: > > I'm resending as I didn't get any response earlier. Hmmm...did you not see my response on Friday? http://archives.postgresql.org/pgsql-novice/2005-04/msg00016.php -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Tue, Apr 05, 2005 at 02:34:59PM -0600, Walker, Jed S wrote: > > I am on 7.3 because the admins installed it (said it was the only version > that could find RPMs for, up til now I've not been concerned). The > production box is going to be 8.0.1 so when that is ready maybe I'll be able > to do this. > > I'll wait until I get 8.0.1 and see what happens. Just to clarify: you'd have the same problem in later versions of PostgreSQL -- I was simply noticing that the version you're running is pretty old and that a lot of other things have been added or fixed since then. But you'll still have to write a function if you want to use PL/pgSQL. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
[Please include the mailing list on replies.] On Tue, Apr 05, 2005 at 02:50:37PM -0600, Walker, Jed S wrote: > > So I can not use an anonymous block (as the plpgsql structure indicates I > can)? What do you mean by "as the plpgsql structure indicates I can"? What part of the documentation are you looking at? If you're looking at the "Structure of PL/pgSQL" section, then remember that it's describing the structure of a PL/pgSQL function -- it mentions nothing about being able to execute anonymous blocks. With current releases of PostgreSQL, if you want to use PL/pgSQL or any other server-side language (PL/Perl, PL/Python, PL/Tcl, etc.), then you must write a function. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
[Please copy the mailing list on replies. I've set this message's Reply-To header to pgsql-novice.] On Tue, Apr 05, 2005 at 03:45:33PM -0600, Walker, Jed S wrote: > yeah, I'm looking at the block structure, but I guess that only applies to > blocks within a function. I'm still not able to get a function to work > though: > > create function it() returns integer as $$ > begin > raise notice 'hello'; > end; > $$ language plpgsql; > > gives errors: > ERROR: parser: parse error at or near "$$" at character 1 You're looking at 8.0 documentation; dollar quotes are a new feature. If you're running 7.3 then you should be looking at 7.3 documentation. http://www.postgresql.org/docs/7.3/static/plpgsql.html > Do I maybe need special privileges to create a function? You shouldn't need special privileges to create a PL/pgSQL function, but you will need to be a database superuser to create the plpgsql language if it's not already in the database. See the documentation for the createlang program (you could also use CREATE LANGUAGE, but createlang is simpler). http://www.postgresql.org/docs/7.3/static/xplang-install.html http://www.postgresql.org/docs/7.3/static/app-createlang.html > Also, it seems that postgres makes special mention that functions are a > security issue, but they also suggest them for reducing client-traffic > messages. What's the deal there? Some languages allow users to write functions that use potentially dangerous operations (e.g., reading and writing files). Dangerous operations are supposed to be restricted to "untrusted" languages that only a database superuser can write functions in. Some languages like PL/Perl have trusted versions that allow only safe operations and untrusted versions that allow just about anything. See for example "Trusted and Untrusted PL/Perl" in the documentation: http://www.postgresql.org/docs/7.3/static/plperl-trusted.html PL/pgSQL is trusted, so in theory it shouldn't allow users to do anything dangerous (nothing that they couldn't already do in SQL, that is). But it's always possible that a bug in the language could cause problems or give users more privilege than they should have. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Thanks Michael, I figured it was something "dumb". We are going to be running on 8.0 so I have been looking at that documentation. I'll have to get this linux box upgraded to 8.0 to avoid any other problems like this. I really appreciate your help! -Jed -----Original Message----- From: Michael Fuhr [mailto:mike@fuhr.org] Sent: Tuesday, April 05, 2005 4:45 PM To: Walker, Jed S Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] plpgsql - can't get a simple block to execute [Please copy the mailing list on replies. I've set this message's Reply-To header to pgsql-novice.] On Tue, Apr 05, 2005 at 03:45:33PM -0600, Walker, Jed S wrote: > yeah, I'm looking at the block structure, but I guess that only applies to > blocks within a function. I'm still not able to get a function to work > though: > > create function it() returns integer as $$ > begin > raise notice 'hello'; > end; > $$ language plpgsql; > > gives errors: > ERROR: parser: parse error at or near "$$" at character 1 You're looking at 8.0 documentation; dollar quotes are a new feature. If you're running 7.3 then you should be looking at 7.3 documentation. http://www.postgresql.org/docs/7.3/static/plpgsql.html > Do I maybe need special privileges to create a function? You shouldn't need special privileges to create a PL/pgSQL function, but you will need to be a database superuser to create the plpgsql language if it's not already in the database. See the documentation for the createlang program (you could also use CREATE LANGUAGE, but createlang is simpler). http://www.postgresql.org/docs/7.3/static/xplang-install.html http://www.postgresql.org/docs/7.3/static/app-createlang.html > Also, it seems that postgres makes special mention that functions are a > security issue, but they also suggest them for reducing client-traffic > messages. What's the deal there? Some languages allow users to write functions that use potentially dangerous operations (e.g., reading and writing files). Dangerous operations are supposed to be restricted to "untrusted" languages that only a database superuser can write functions in. Some languages like PL/Perl have trusted versions that allow only safe operations and untrusted versions that allow just about anything. See for example "Trusted and Untrusted PL/Perl" in the documentation: http://www.postgresql.org/docs/7.3/static/plperl-trusted.html PL/pgSQL is trusted, so in theory it shouldn't allow users to do anything dangerous (nothing that they couldn't already do in SQL, that is). But it's always possible that a bug in the language could cause problems or give users more privilege than they should have. -- Michael Fuhr http://www.fuhr.org/~mfuhr/