Re: plpgsql - can't get a simple block to execute

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(at)fuhr(dot)org]
Sent: Tuesday, April 05, 2005 4:45 PM
To: Walker, Jed S
Cc: pgsql-novice(at)postgresql(dot)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/